Skip to content

Instantly share code, notes, and snippets.

@aiwilliams
Created November 14, 2012 15:40
Show Gist options
  • Save aiwilliams/4072840 to your computer and use it in GitHub Desktop.
Save aiwilliams/4072840 to your computer and use it in GitHub Desktop.
SQL JOIN multiple has_many through without duplicates
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
>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment