SQL join 4 table

Question

1) user-  id | nickname 
           5    hello
           6    ouuu
           7    youyou
2) team_leader-  id | user_id | team_name | game |position
                  2      5        haha      M.A     Leader
                  3      7        nono      M.A     Leader
3) team_member-  id | user_id | team_leader_id| game |position
                  1      6         2            M.A    Member

4) user_game-    id | user_id | game | character_game
                  1      5      M.A     wahaha
                  2      6      M.A     kiki
                  3      7      M.A     popo

我想显示team_leader的 ID，其中 id=2所以输出应该显示：
昵 称 角色名称    职位
你好 哇哈哈             领袖           
欧乌              琪琪                 

Answer 1

首先，切勿在查询中使用*，只要它们不仅用于临时测试目的。即使一切按预期工作，也可能导致以后出现意外错误，例如当表模式被更改时。始终明确列出要包含在查询中的字段。

查询的问题在于联接表中存在名称冲突。每个表都有一个id字段，因此您必须使用别名并通过限定名称对它们进行寻址，以便能够清楚地区分它们。

SELECT u.id, u.nickname, t.id, t.user_id, t.team_name, t.game,
    tm.id, tm.user_id, tm.team_id, ug.id, ug.user_id, ug.game, ug.character_game
    FROM user AS u
    JOIN user_game AS ug
    ON ug.user_id = u.id 
    JOIN team AS t
    ON t.user_id = u.id
    JOIN team_member AS tm
    ON t.id = tm.team_id
    WHERE t.id = 2 AND ug.game = t.game
    AND tm.team_id = 2;

顺便说一句：如果您的应用程序设计允许用户只能有一部分"子表"引用user_id甚至没有，您应该考虑使用 LEFT OUTER JOIN .这将确保每个用户在结果中都有一行，即使是那些不是团队成员的用户，例如

SELECT u.id, u.nickname, t.id, t.user_id, t.team_name, t.game,
    tm.id, tm.user_id, tm.team_id, ug.id, ug.user_id, ug.game, ug.character_game
    FROM user AS u
    LEFT OUTER JOIN user_game AS ug
    ON ug.user_id = u.id 
    LEFT OUTER JOIN team AS t
    ON t.user_id = u.id
    LEFT OUTER JOIN team_member AS tm
    ON t.id = tm.team_id
    WHERE t.id = 2 AND ug.game = t.game
    AND tm.team_id = 2;

Answer 2

 select * from user u
inner join team t on u.id = t.user_id
inner join team_member tm on t.user_id = tm.user_id
inner join user_game ug on t.user_id = ug.user_id and t.game = ug.game where tm.team_id = 2 

关注精益 SQL 加入

Answer 3

试试这个：

SELECT 
a.nickname,
b.position,
c.character_game
FROM user a
INNER JOIN team_leader b ON b.user_id=b.id
INNER JOIN user_game c ON c.id=c.user_id
WHERE a.id=5 OR a.id=6