3

So I have 3 tables:

  • users
  • achievements
  • achievements_unlocked (many-to-many)

I would like a query to return all achievements available, plus mention what achievements the current user (id_user=123 for example) has unlocked.

How can I do this? This should involve a LEFT JOIN but if I add "WHERE id_user = 123" it won't get me achievements locked...

Thanks!

Etienne
  • 251
  • 5
  • 15

2 Answers2

4

Try this

SELECT ac.*, 
    CASE WHEN un.ach_id IS NULL THEN 0
    ELSE 1 END AS user_unlocked
FROM achievements ac
    LEFT JOIN 
        (SELECT au.ach_id FROM achievements_unlocked au
         INNER JOIN users u ON au.user_id = u.id
         WHERE u.id = 123) un
        ON ac.id = un.ach_id
Marco
  • 56,740
  • 14
  • 129
  • 152
2
Select *
From achievements
Left Outer Join achievements_unlocked On achievements_unlocked.achievement_id = achievements.achievement_id
Left Outer Join users On
    users.user_id = achievements_unlocked.user_id And
    users.user_id = 123

This will likely be the most efficient way to do this without having to do nested loops and hash matching in the execution plan. Make sure you have indexes on the achievement_id and user_id columns on all tables that contain them.

Jordan
  • 31,971
  • 6
  • 56
  • 67
  • Doesn't work as you wrote it; it basically ignored "And users.user_id = 123". I replaced "and" with "having" and now it seems to work. I guess it does the trick even if it's not as effective! Thanks – Etienne Dec 05 '11 at 00:23