MySQL tricky double / triple JOIN (?)

database, join, mysql, social-networking, sql

Solution

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>

Problem

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`?

Original source