SQL Query to return Yes/No Based on a Subquery with Many-Many Join

Question

I have a many-to-many relational schema with 3 tables: Users, Teams, and Teamuser.

Since a user can be on many teams and a team can have many users, teamuser is a table that joins users.id to teams.id.

I'd like to do a query that asks "With a given user ID, show me all of the teams in the teams table, with a calculated field called "member" that returns 1 if the given user is a member of that team, and a 0 otherwise"

Is there a way to do this with one query directly in MySQL?

TeamUser:

id teamid userid
5 1 [->] 1 [->]
1 1 [->] 2 [->]
2 2 [->] 2 [->]
6 3 [->] 1 [->]

teamid is a foreign key joined to Teams.id. userid is a foreign key on Users.id

Teams:

id name
1 Whomping Willows
2 Chudley Cannons

Users:

id username 
1 fred
2 finn 
3 paul 
16 pickles

• Abhik Chakraborty: show the table schema and how they are related.
• z3ugma: Updated with the schema
• Sebas: why does your title mention many many joins and a subquery?

Answer 1

Run something like the following to get the Cartesian product and then do the correlated sub-select wrapped in a coalesce to indicate membership?

select u.username, t.teamname, COALESCE((select 1 from teamusers itu where itu.teamid = t.teamid and itu.userid = u.userid), 0) from users u, teams t where u.username = 'finn'

Warning though this will not scale well as it does a select from team users for each row in the outer Cartesian product. More teams == more rows to run the sub-select

Answer 2

Here how you can do it

select
t.id,
t.name,
case when x.userid is not null then 1 else 0 end as `member`
from teams t
left join (
 select 
 tu.userid,
 tu.teamid
 from teamuser tu 
 left join users u on u.id = tu.userid 
 where u.id = 1 -- change the user id here which you are looking at
)x
on x.teamid = t.id

Answer 3

If i understand you correctly, you want to display result with two columns: first = Team, second - Member. Result set will consist with all of the teams, and member column - is given username is a member of the team. Then, you can use following query:

SELECT a.name AS team_name,
 CASE WHEN c.username = 'fred' THEN 1 ELSE 0 END AS member 
 FROM Teams AS a JOIN TeamUser AS b ON a.id = b.teamid
 JOIN Users AS c ON b.userid = c.id;

• z3ugma: This returns more than one row per team

Answer 4

(Updated after actually testing on MySQL rather than Oracle.)

You could work along

set @userID = 1;
SELECT
 TeamsOuter.name
 , CASE IFNULL((SELECT Users.id FROM Users JOIN TeamUser ON Users.id = TeamUser.userID JOIN Teams ON TeamUser.teamID = Teams.id WHERE Teams.id = TeamsOuter.id AND Users.id = @userID), 0) WHEN 0 THEN 0 ELSE 1 END member
 , IF((SELECT Users.id FROM Users JOIN TeamUser ON Users.id = TeamUser.userID JOIN Teams ON TeamUser.teamID = Teams.id WHERE Teams.id = TeamsOuter.id AND Users.id = @userID) IS NULL, 0, 1) memberToo
FROM Teams TeamsOuter
;

using either CASE or IF.
As the question is explicit on "With a given user ID [...]", this is being used in the query.

NB: When checking the results: "TeamUser" does reference a team with "teamID" 3, which does not exist...

If you wanted actually everything in one place:

SELECT
 UsersOuter.userName
 , TeamsOuter.name
 , IF((SELECT Users.id FROM Users JOIN TeamUser ON Users.id = TeamUser.userID JOIN Teams ON TeamUser.teamID = Teams.id WHERE Teams.id = TeamsOuter.id AND Users.id = UsersOuter.id) IS NULL, 0, 1) member
FROM Teams TeamsOuter, Users UsersOuter
ORDER BY UsersOuter.userName, TeamsOuter.name
;

SQL Fiddle