sql query with 3 tables with multiple values column

I have 3 tables:

participants (id, name)
groups (id, name)
participant_groups (participant_id, group_id)

I need to show a table with all the participants and a last column with the participant’s groups, which can be several.

I have the following select that returns all the participants but if it has more than one group I have several records of the same participant.

SELECT *
    FROM participants
      CROSS JOIN groups
      INNER JOIN participant_groups
        ON groups.id = participant_groups.id
        AND participants.id = participant_groups.id
    ORDER BY participants.name, participants.id

Thanks

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Just add a GROUP BY and GROUP_CONCAT:

SELECT participants.id, participants.name, GROUP_CONCAT(groups.name) AS group_list
FROM participants
LEFT JOIN participant_groups ON participants.id = participant_groups.participant_id
INNER JOIN groups ON participant_groups.group_id = groups.id
GROUP BY participants.id

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0