sql query with 3 tables with multiple values column-CodePudding

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

CodePudding user response：

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