mysql: Select all rows and compare with other table

I have two tables and need to get all rows from the first one and then check which values from the second table match the first table.

My goal is to get all so called ‘achievements’ and then check which one the user has reached.

achievements

+---------------+-------------+
| achievementID | description |
+---------------+-------------+
| 1             | goal1       |
| 2             | goal2       |
| 3             | goal3       |
+---------------+-------------+

achievement_user

+---------------------+---------------+--------+
| achievementRecordID | achievementID | userID |
+---------------------+---------------+--------+
| 1                   | 1             | 1      |
| 2                   | 1             | 3      |
| 3                   | 4             | 2      |
| 4                   | 3             | 1      |
+---------------------+---------------+--------+

My desired result for a query where I check the results for userID = 1 would be something like

+---------------+---------------+--------------+
| achievementID | description   | solvedByUser |
+---------------+---------------+--------------+
| 1             | goal1         | true         |
| 2             | goal2         | false        |
| 3             | goal3         | true         |
+---------------+---------------+--------------+

The new column solvedByUser could be basically any datatype (boolean, int, …). I just need a list of all available achievements and then see which one the user has reached.

Answer

You can left join the achievments table with achievement_user:

select a.*, (au.userID is not null) solvedByUser
from achievements a
left join achievement_user au 
    on  au.achievementID = a.achievementID
    and au.userID = 1

solvedByUser is a 0/1 flag that indicates whether the given achievement was reached by the given user.

Advertisement

Answer