public
Created

SQL JOIN multiple has_many through without duplicates

  • Download Gist
gistfile1.txt
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
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
 
>

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.