SQL JOIN multiple has_many through without duplicates

gistfile1.txt

sqlite3 test.db

> create table events(_id smallint, name varchar(10));
create table speakers(_id smallint, name varchar(10));
create table terms(_id smallint, name varchar(10));
create table events_speakers(event_id smallint, speaker_id smallint);
create table events_terms(event_id smallint, term_id smallint);
insert into events values(1, 'Soccer');
insert into events values(2, 'Baseball');
insert into events values(3, 'Football');
insert into speakers values(1, 'Bobby');
insert into speakers values(2, 'Jane');
insert into terms values(1, 'Ball');
insert into terms values(2, 'Helmets');
insert into terms values(3, 'Bat');
insert into events_speakers values(1, 1);
insert into events_speakers values(2, 1);
insert into events_speakers values(3, 1);
insert into events_speakers values(3, 2);
insert into events_terms values(1, 1);
insert into events_terms values(2, 1);
insert into events_terms values(2, 2);
insert into events_terms values(2, 3);
insert into events_terms values(3, 1);
insert into events_terms values(3, 2);

> SELECT events._id, events.name, group_concat(distinct speakers.name) AS speaker_names, group_concat(distinct terms.name) AS term_names FROM events LEFT OUTER JOIN (SELECT et.event_id, ts.name FROM terms ts JOIN events_terms et ON ts._id = et.term_id) terms ON events._id = terms.event_id LEFT OUTER JOIN (SELECT sp._id, es.event_id, sp.name FROM speakers sp JOIN events_speakers es ON sp._id = es.speaker_id) speakers ON events._id = speakers.event_id GROUP BY events._id;

1|Soccer  |Bobby     |Ball
2|Baseball|Bobby     |Ball,Bat,Helmets
3|Football|Bobby,Jane|Ball,Helmets

>