MySQL - List members of the same group per row

Question

I have 2 tables

tbl_Participants

id group_name firstname lastname
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

First and last names will be showed under member col
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

please orvide data for an ]mre] and also read https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query what you want is to pivot like https://stackoverflow.com/a/62712027/5193536 — nbk, May 31 '21 at 14:04
First what is the point of joining the group table since you don't use anything from it as the groupname is already in the participant table ? — Thomas G, May 31 '21 at 14:15
@ThomasG Joining the tables for listing down the members per group once you hit the Edit button. — Romanov, Jun 01 '21 at 07:21

forpas · Accepted Answer · 2021-05-31T15:55:07.927

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
)

I just realized that these queries list the results in the requested format, groups by group_name col but won't work with an EDIT function that uses ID. I will think of a different approach in order to add/remove participants by the group. Probably edit by Participant level if I can't think of an approach based on the queries above. Thank you — Romanov, Jun 01 '21 at 11:26

MySQL - List members of the same group per row

1 Answers1