I have three tables, user/team/user_team(many-to-many)
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)
I want to get all team records information with associated user information like below
--------------
tid tname username
1 t1 Tom
2 t2
3 t3
Can you tell me how to write the sql statement?
Sorry for my mistake. I've updated my question by adding one more record t3 in team table.
try this:
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments