Im having the following problem:
I try to implement an achievementsystem. I have two tables. Table 1 contains the achievement_id and achievement_info. Table 2 contains the link to the user, meaning achievement_id and player_id, so that you can tell which user has achieved certain things.
I'm trying to write a method that returns me all achievements, but additionally a flag that tells me if a certain user has achieved this row or not.
E.g.: getPlayerAchievements(playerid) --> returns a list of Achievements with id, info, and a bool flag whether the user has achieved it.
1 |info1 |true
2 |info2 |false
3 |info3 |true
You want to select all records from the achievemets table and show them. That's the easy part :-) For every record you want to show whether player 1234 has attained this achievement. You can do this with an
select achievement_id, achievement_info, exists ( select * from players p where p.player_id = 1234 and p.achievement_id = a.achievement_id ) as achieved from achievements a;
Or even simpler with
select achievement_id, achievement_info, achievement_id in (select achievement_id from players where player_id = 1234) as achieved from achievements;