CODEWITHSUNDEEP
phpmysql
Romanov
Romanov

Reputation: 41

MySQL - List members of the same group per row

I have 2 tables

  1. tbl_Participants

    id group_name firstname lastname

  2. tbl_Groups

    id group_name

But I have difficulties joining the tables to show the below layout.

Group Name | Member 1 | Member 2 | Member 3
  1. First and last names will be showed under member col
  2. Max of 3 participants per group

This query shows a list but not in the layout above:

select gr.firstname, gr.lastname, gr.group_name 
from tbl_Participants gr inner join tbl_Group gr2 
ON gr.group_name = gr2.group_name 
order by group_name

Upvotes: 1

Views: 68

Answers (1)

forpas
forpas

Reputation: 164139

For MySQl 8.0+ you can use ROW_NUMBER() window function to rank member names inside their groups alphabetically and after filtering only the first 3 names of each group use conditional aggregation to get the result that you want:

SELECT group_name,
       MAX(CASE WHEN rn = 1 THEN name END) Member1,
       MAX(CASE WHEN rn = 2 THEN name END) Member2,
       MAX(CASE WHEN rn = 3 THEN name END) Member3
FROM (
  SELECT group_name, 
         CONCAT(lastname, ' ', firstname) name,
         ROW_NUMBER() OVER (PARTITION BY group_name ORDER BY lastname, firstname) rn
  FROM tbl_Participants
) t
WHERE rn <= 3
GROUP BY group_name

For prior versions use aggregation with GROUP_CONCAT() and SUBSTRING_INDEX() to split the names list:

SELECT group_name,
       SUBSTRING_INDEX(names, ',', 1) Member1,
       CASE WHEN counter > 1 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(names, ',', 2), ',', -1) END Member2,
       CASE WHEN counter > 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(names, ',', 3), ',', -1) END Member3
FROM (
  SELECT group_name, 
         COUNT(*) counter,
         GROUP_CONCAT(CONCAT(lastname, ' ', firstname) ORDER BY lastname, firstname) names
  FROM tbl_Participants
  GROUP BY group_name
) t

See a simplified demo.

If there are groups in tbl_Groups with no rows in tbl_Participants and you want them in the results, it's easier to do it with UNION ALL than a join.
Add to the above queries:

UNION ALL
SELECT g.group_name, null, null, null
FROM tbl_Groups g
WHERE NOT EXISTS (
  SELECT 1 
  FROM tbl_Participants p
  WHERE p.group_name = g.group_name
)

Upvotes: 1

Related Questions