CODEWITHSUNDEEP
mysqlsql
medo ampir
medo ampir

Reputation: 1910

how to select all from table and its matches from another?

i have two tables:

TblAchievements>> [ID, AchName]

TblUsersAchievements>> [UserID, AchID]

i need the results to be like this (assuming that there are 4 achievements and the user 1 have achieved 1 and 2:

 ID     AchName     UserID     AchID
 1      first         1         1
 2      second        1         2
 3      third        NULL      NULL
 4      fourth       NULL      NULL

i tried some thing like this:

 SELECT 
   tblacheivements.id,
   tblacheivements.achname,
   tbluserachievements.uid,
   tbluserachievements.achid
 FROM
   tbluserachievements
 RIGHT OUTER JOIN tblacheivements ON (tbluserachievements.achid = tblacheivements.id)
 WHERE
   tbluserachievements.uid = 1 OR 
   tbluserachievements.uid IS NULL

it gets the correct result but when i change the user the results are wrong.

Upvotes: 0

Views: 97

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

It seems like you want to list out all achievements a particular user has or doesn't have. To filter on the UserID, but still retain each achievement row in the result-set, you have to include the UserID selection in the outer-join condition (putting it in the WHERE clause will only select rows where the user achieved the achievement since WHERE is evaluated after FROM / JOIN.)

Try this solution:

SELECT    *
FROM      tblachievements a
LEFT JOIN tbluserachievements b ON a.id = b.achid AND b.uid = 1

Extra:

Depending on your application requirements, perhaps it may be better to simply have a boolean value indicating whether the user has achieved it or not (1 if they did, 0 if not):

SELECT    a.*, b.achid IS NOT NULL AS achieved
FROM      tblachievements a
LEFT JOIN tbluserachievements b ON a.id = b.achid AND b.uid = 1

Which would produce:

 id     achname     achieved
 ---------------------------
 1      first       1     
 2      second      1     
 3      third       0   
 4      fourth      0

Upvotes: 2

Related Questions