Created
November 14, 2012 15:40
-
-
Save aiwilliams/4072840 to your computer and use it in GitHub Desktop.
SQL JOIN multiple has_many through without duplicates
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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