mysql - 如何获取另一个表的字段信息

Question

我有三个表，user/team/user_team(多对多)

user
-----------
id  name 
1   Tom
2   Jerry
3   John

team
------------
id   name
1    t1
2    t2
3    t3

user_team
---------------------
userid   teamid  isdeleted
1        t1       0   <----(0 means not deleted record, which can be searched out)
2        t2       1   <----(1 means deleted record, which can not be searched out)

我想获取所有团队记录信息以及关联的用户信息，如下所示

--------------
tid tname  username
1   t1     Tom
2   t2     
3   t3

能告诉我sql语句怎么写吗？

很抱歉我的错误。我通过在团队表中添加一条记录 t3 更新了我的问题。

Answer 1

试试这个:

Select a.id as tid
a.name as tname
b.name as username
from team a
LEFT JOIN user_team c on a.name = c.teamid and c.isdeleted = 0
LEFT JOIN user b on b.id = c.userid