Given a table of "events" where each event may be associated with zero or more "speakers" and zero or more "terms", those records associated with the events through join tables, I need to produce a table of all events with a column in each row which represents the list of "speaker_names" and "term_names" associated with each event.
However, when I run my query, I have duplication in the speaker_names and term_names values, since the join tables produce a row per association for each of the speakers and terms of the events:
2|Baseball|Bobby - Bobby - Bobby|Ball - Bat - Helmets
3|Football|Bobby - Jane - Bobby - Jane|Ball - Ball - Helmets - Helmets
Look up the speaker/term names independently from each other:
SELECT _id, name, (SELECT GROUP_CONCAT(name, ';') FROM events_speakers JOIN speakers ON events_speakers.speaker_id = speakers._id WHERE events_speakers.event_id = events._id ) AS speaker_names, (SELECT GROUP_CONCAT(name, ';') FROM events_terms JOIN terms ON events_terms.term_id = terms._id WHERE events_terms.event_id = events._id ) AS term_names FROM events