MySQL tricky double / triple JOIN (?)

Question

Table structure:

    users: uid, name
    friendships: uid1, uid2
    game_membership: gid, uid

basically I'm trying to get a query as a function of gid, and return:

uid1, name1, uid2, name2

where only the uid's that are in the game_membership table under the given gid are returned. this is what I've got so far:

SELECT 
   f.uid1 AS id1, 
   u1.name AS name1, 
   f.uid2 AS id2, 
   u2.name AS name2
FROM 
   friendships AS f
WHERE 
   u1.uid in (SELECT uid FROM game_membership WHERE gid = <GID>)
   AND u2.uid in (SELECT uid FROM game_membership WHERE gid = <GID>)
JOIN users AS u1 ON f.uid1 = u1.uid
JOIN users AS u2 ON f.uid2 = u2.uid

I just get a MySQL syntax error right now. Can anyone point me in the right direction? I feel like maybe I have to do another JOIN or two on the uid's in the game_membership table, one for each uid?

Answer 1

I would suggest you join to the Game_membership table instead of the where clause:

SELECT 
   f.uid1 AS id1, 
   u1.name AS name1, 
   f.uid2 AS id2, 
   u2.name AS name2
FROM 
   friendships AS f
JOIN game_membership AS user1_gm ON user1_gm.uid = f.uid1
JOIN game_membership AS user2_gm ON user2_gm.uid = f.uid2
JOIN users AS u1 ON f.uid1 = u1.uid
JOIN users AS u2 ON f.uid2 = u2.uid
WHERE user1_gm.gid = <GID> AND user2_gm.gid = <GID>