Mysql - Display Team name and Count of Team members from 2 tables

Question

The following query works great

SELECT
          t.name, 
          t.id
        FROM
          team t,
          member m
        WHERE
          m.team_id = t.id

and shows multiple results what I am stuck with it is with how to modify the query about to display the team name and the number of team members in that team so, for example, Team A has 50 team members, Team B has 20 members and so on.

The problem is that the member.team_id has Comma separated values

My table structure for team table

My table structure for member table

Not a duplicate question at all

Are you stuck with that table structure? I can't think of a use case for using comma-separated values in a database instead of a linking table. — SolarBear, Dec 22 '16 at 20:47
@SolarBear not with the table structure but with the query, Unfortunately I cannot change the tables, so was wondering if the query can be modified to display the team name and the respective counts (team members) — user580950, Dec 22 '16 at 20:48
See this question : http://stackoverflow.com/questions/19073500/sql-split-comma-separated-row — SolarBear, Dec 22 '16 at 20:51
@SolarBear I tried GROUP BY t.name but how do I add the count to the query to display the right results — user580950, Dec 22 '16 at 20:51
It's is not a great idea to store the team members as a comma separated list, and I also don't see how that query works. For example you are joining an integer to a varchar — Paul Maxwell, Dec 22 '16 at 21:00
Your original query won't work correctly, it will only match the first team in `member.team_id`. To make it work correctly, you need to use `FIND_IN_SET`. See http://stackoverflow.com/questions/25611999/sql-join-tables-where-1-column-has-comma/25612053#25612053 — Barmar, Dec 22 '16 at 21:01
@Used_By_Already since team_id is stored as comma separated values it has to be varchar, I understand that its not the right way, but I do not have enough permission to change the table structure. — user580950, Dec 22 '16 at 21:02
but what I am saying is that your query is NOT great. I don't even know how i works at all. Are you certain you query does not join using t.id = m.id ? — Paul Maxwell, Dec 22 '16 at 21:05
@Barmar I tried SELECT * FROM team t JOIN member m ON ( FIND_IN_SET( m.team_id, t.id ) >0 ) LIMIT 0 , 30 it shows right records but how do I count those and display team name here? — user580950, Dec 22 '16 at 21:08
`SELECT t.name, COUNT(*) FROM team t JOIN member m ON FIND_IN_SET( m.team_id, t.id ) >0 GROUP BY t.name` — Barmar, Dec 22 '16 at 21:10
That's the same way you would get the count if you were using a more normal way to join the tables. — Barmar, Dec 22 '16 at 21:11
@Barmar this works great, please post your answer and I will accept :) — user580950, Dec 22 '16 at 21:14
@Barmar you should be able to post now SELECT t.name, COUNT( * ) FROM team t JOIN member m ON FIND_IN_SET( m.team_id, t.id ) >0 GROUP BY t.name LIMIT 0 , 30 — user580950, Dec 22 '16 at 21:14

score 1 · Accepted Answer · edited May 23 '17 at 12:33

1

Use the technique in sql join tables where 1 column has comma to join the tables, then use COUNT(*) to get the member counts.

SELECT t.name, COUNT(*) 
FROM team t 
JOIN member m 
ON FIND_IN_SET( m.team_id, t.id ) > 0 
GROUP BY t.name

edited May 23 '17 at 12:33

Community

1
1

answered Dec 22 '16 at 21:15

Barmar

741,623
53
500
612

score 0 · Answer 2 · answered Dec 22 '16 at 20:58

0

To get the number of members from comma separated list try using length() and replace()

select
(LENGTH(team_ids) - LENGTH(REPLACE(team_ids, ',', '')))+1
from MyTable

By removing the commas the length is reduced by the number of those, and ou need 1 more because there isn't a trailing comma at the end.

answered Dec 22 '16 at 20:58

Paul Maxwell

33,002
3
32
51

Not sure how this will be used in a join query, tried this but it does not give the intended results select t.name, t.id FROM team t, member m where (LENGTH(m.team_id) - LENGTH(REPLACE(m.team_id, ',', '')))+1 – user580950 Dec 22 '16 at 21:04
it is NOT used in the where clause, use it is the select clause to display the number of team members, that is what you asked for isn't it? – Paul Maxwell Dec 22 '16 at 21:07
@Used_By_Already This is backwards. This will return the number of teams that each member is on, not the number of members of each team. – Barmar Dec 22 '16 at 21:09

Mysql - Display Team name and Count of Team members from 2 tables

2 Answers2