Tony Karam Tony Karam - 1 year ago 68
SQL Question

MySQL statement to read data from one table with checks on another table

I have these two tables:






I want to retrieve rows from table Achievement. But, I do not want all the rows, I want the rows that a specific Steam ID has acquired. Let's take STEAM_0:0:46481449 for example, I want to check first the list of IDs that STEAM_0:0:46481449 has acquired (4th column in Achieves table states whether achievement is acquired or not) and then read only those achievements.

I hope that made sense, if not let me know so I can explain a little better.

I know how to do this with two MySQL statements, but can this be done with a single MySQL statement? That would be awesome if so please tell me :D

EDIT: I will add the two queries below

SELECT * FROM Achieves WHERE Achieves.SteamID = 'STEAM_0:0:46481449' AND Achieves.Acquired = 1;

Then after that I do the following query

SELECT * FROM Achievement;

And then through PHP I would check the IDs that I should take and output those. That's why I wanted to get the same result in 1 query since it's more readable and easier.

Answer Source

In sql left join, applying conditions on second table will filter the result when join conditions doesn't matter:

   Select * from achievement 
   left join achieves on ( 
   where achieves.acquired=1

Besides,I suggest not using ID in the achieves table as the shared key between two tables. Name it something else.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download