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
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.
Just add a
GROUP BY and
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