Question

mysql 多表联合查询问题

我现在有3个表(用户表user, 团队team, 团队成员teammember)

表user
uid username password
1 user1 pw1
2 user2 pw2
...

表team
tid teamname
1 team1
2 team2
...

表teammember
id uid tid status
1 3 2 1
2 1 1 0
3 4 1 1
4 2 2 1
...

我现在想通过用户表的username 和password
来取得user.uid team.teamname 和 teammember.status
如果用户不在任何团队就只返回user.uid

Answer 1

以下语句可以实现通过用户表的username 和password来取得user.uid team.teamname 和 teammember.status
但册握培如果用户不在任何团队还会返team.teamname 和 teammember.status最好是能州唯通过前端程序去处理这皮粗个问题

select teammember.uid,team.teamname,teammember.status
from teammember left join team on Teammember.tid = team.tid
where uid=(select uid
from user
where username='user1' and password='pw1');