DrSkyer DrSkyer - 1 year ago 66
SQL Question

Get a certain query result from two different mysql tables

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.

table 1:

achievement_id|achievement_info
1 |info1
2 |info2
3 |info3


table 2:

achievement_id|player_id;
1 |15
3 |15


the result I need by entering the player_id "15":

achievement_id|achievement_info|(bool)achieved
1 |info1 |true
2 |info2 |false
3 |info3 |true


I already have the achievement class so I just have to fill them with my data.

I could always use two seperate sql queries to achieve that, but I thought maybe there was a way to simplify it, since I use php to get my data and don't want two connections and queries in one php script.

Answer Source

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 EXISTS clause:

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 IN:

select 
  achievement_id, 
  achievement_info,
  achievement_id in (select achievement_id from players where player_id = 1234) as achieved
from achievements;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download