CODEWITHSUNDEEP
mysqlsqlselectjoin
SquigglyFrog
SquigglyFrog

Reputation: 59

Mysql query - help getting data from 2 tables

I can handle simple mysql stuff, with single tables normally, but im working on / modifying a script to pull game achievements from a database which has 2 tables.. and this is accessing two tables at once.. so im a little lost. I can understand the line below:

SELECT * FROM achievables 
WHERE gameid = '1' 
AND achievementid IN 
(
    SELECT achievementid 
    FROM achievements 
    WHERE userid = '1111'  
    AND gameid = '1'
)

This works almost perfect.. however what I want is to also get the DATE field from that second table as well.. I would have thought it was a simple achievementid, date - but that doesnt pull any records, nor does using a SELECT * in the 2nd part..

Upvotes: 1

Views: 34

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

You just have to simply use JOIN instead of IN

Try this: 

SELECT * 
FROM achievables A 
INNER JOIN achievements B ON A.achievementid = B.achievementid AND A.gameid = B.gameid AND B.userid = '1111' 
WHERE A.gameid = '1';

Upvotes: 0

juergen d
juergen d

Reputation: 204766

You need to join the tables

SELECT a1.*, a2.date
FROM achievables a1
JOIN achievements a2 on a1.achievementid = a2.achievementid
                    and a1.gameid = a2.gameid 
WHERE a1.gameid = '1' 
AND a2.userid = '1111'

Upvotes: 2

Related Questions