CODEWITHSUNDEEP
mysqlsql
Littlebob
Littlebob

Reputation: 153

Need help writing SQL SELECT statement that combines two tables

I have two tables: team and team_member. The team_member table shows which user is a member of which team.

+---------+------------+
| team_id |    name    |
+---------+------------+
|       1 | Marketing  |
|       2 | New Launch |
|       3 | Sales      |
+---------+------------+

+---------+---------+  
| user_id | team_id |  
+---------+---------+  
|      21 |       2 |  
|    4369 |       1 |  
|    4369 |       2 |  
|    4369 |       3 |  
|     100 |       1 |  
|      21 |       1 | 
+---------+---------+

I need a query that tells me the team_id and team name for all teams that a given user_id is a member of and also (here's the difficult part) a count of all members in each team.

For the above data, if we're looking at user 21, I expect to see the following output because user 21 is a member of team 1 and team 2 and team 1 has 3 members and team 2 has 2 members.

+---------+------------+-------+
| team_id |    name    | count |
+---------+------------+-------+
|       1 | Marketing  |     3 |
|       2 | New Launch |     2 |
+---------+------------+-------+

Any idea how I can do this in SQL (MySQL preferably if that makes a difference)? Getting the first 2 columns is no problem (see bottom) but I can't figure out how to add the count. Thanks!

SELECT team.team_id, team.name
FROM team
LEFT JOIN team_member ON team.team_id = team_member.team_id
WHERE team_member.user_id = 21

Upvotes: 0

Views: 112

Answers (4)

mickmackusa
mickmackusa

Reputation: 47904

You can use a pivot query.

SELECT t.team_id,
       MAX(IF(m.user_id = 21, t.name,  NULL)) AS name,
       COUNT(m.user_id) AS count
FROM team t
JOIN team_member m ON t.team_id = m.team_id
GROUP BY t.team_id
HAVING name IS NOT NULL

Only generate the team name column from the aggregate data if 21 is found on the team.

Output:

team_id |    name    | count
   1    | Marketing  |   3
   2    | New Launch |   2

Upvotes: 0

alex067
alex067

Reputation: 3281

SELECT team.team_id, team.name, count(team_member.team_id)
FROM team
INNER JOIN team_member ON team.team_id = team_member.team_id
GROUP BY (team.team_id, team.name)
WHERE team_member.user_id = 21

Upvotes: 0

forpas
forpas

Reputation: 164099

You can join team_member twice with team and then aggregate:

SELECT t.team_id, t.name, COUNT(mm.user_id) count
FROM team_member m
INNER JOIN team t ON t.team_id = m.team_id
INNER JOIN team_member mm ON mm.team_id = t.team_id
WHERE m.user_id = 21
GROUP BY t.team_id, t.name

See the demo.
Results:

| team_id | name       | count |
| ------- | ---------- | ----- |
| 1       | Marketing  | 3     |
| 2       | New Launch | 2     |

Upvotes: 3

gerpes8
gerpes8

Reputation: 45

Do count(teamid) then group by name or teamid, then you will need to do having instead of where to filter by teamid or name.

Upvotes: -1

Related Questions