MySQL 棘手的双重/三重 JOIN(？)

Question

表结构:

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

基本上，我试图将查询作为 gid 的函数，并返回:

uid1, name1, uid2, name2

其中仅返回给定 gid 下 game_membership 表中的 uid。这是我到目前为止所得到的:

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

我现在刚收到一个 MySQL 语法错误。谁能指出我正确的方向？我觉得也许我必须在 game_membership 表中的 uid 上再做一两次 JOIN，每个 uid 一个？

Answer 1

我建议您加入 Game_membership 表而不是 where 子句:

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>