# Complex SQL Query **URL:** \ **Category:** Factual Questions\ **Created:** [March 25, 2014, 12:20pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518 "2014-03-25T12:20:17Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Eutychus](https://avatars.discourse-cdn.com/v4/letter/e/a3d4f5/32.png) [@Eutychus](https://boards.straightdope.com/u/Eutychus)\ **Post date:** [March 25, 2014, 12:20pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/1 "2014-03-25T12:20:17Z")
Let’s say that I have a database (and, by coincidence, I do!) of cartoons,linked to a table of the DVDs they appear in, and that linked to the people who do the commentaries on each cartoon for that DVD. So, if I do a query for that DVD, the cartoons and the commentators, it might return something like this: ```auto DVD Title Commentators ----------------------------------------------------------------------------------------- The Simpsons Season 10 Lard of the Dance Mike Scully The Simpsons Season 10 Lard of the Dance Ron Hague The Simpsons Season 10 Bart the Mother Matt Groening The Simpsons Season 10 Bart the Mother Mike Scully The Simpsons Season 10 Bart the Mother George Meyer ``` But now, lets say that I had a cartoon that didn’t have any commentators. The SQL doesn’t return anything since there’s nothing to link it to at the end. Is there anyway to have the SQL return at least one entry, even if the last one has a null value for the commentator? Like this: ```auto DVD Title Commentators ----------------------------------------------------------------------------------------- The Simpsons Season 10 Lard of the Dance Mike Scully The Simpsons Season 10 Lard of the Dance Ron Hague The Simpsons Season 10 Bart the Mother Matt Groening The Simpsons Season 10 Bart the Mother Mike Scully The Simpsons Season 10 Bart the Mother George Meyer The Simpsons Season 10 Lisa gets an A NULL ``` ---
**Author:** ![Spud](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/spud/32/347_2.png) [@Spud](https://boards.straightdope.com/u/Spud)\ **Post date:** [March 25, 2014, 12:26pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/2 "2014-03-25T12:26:15Z")
Running out the door, but an outer or left join is what you need. If nobody else has added info I’ll post more later. ---
**Author:** ![Athena](https://avatars.discourse-cdn.com/v4/letter/a/35a633/32.png) [@Athena](https://boards.straightdope.com/u/Athena)\ **Post date:** [March 25, 2014, 12:31pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/3 "2014-03-25T12:31:04Z")
What **Spud** said; what you want is an outer join. So if your original query was something like: Select DVD, Title, Commentators from tableDVDs join tableCommmentators on tableDVDs.dvdID = tableCommentators.dvdID it’d change to something like: Select DVD, Title, Commentators from tableDVDs LEFT OUTER join tableCommmentators on tableDVDs.dvdID = tableCommentators.dvdID CAVEAT: I’ve only had one cup of coffee and I don’t know your table structure or the exact brand of SQL you’re using. But outer join is definitely your friend here. ---
**Author:** ![Keeve](https://avatars.discourse-cdn.com/v4/letter/k/f07891/32.png) [@Keeve](https://boards.straightdope.com/u/Keeve)\ **Post date:** [March 25, 2014, 12:54pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/4 "2014-03-25T12:54:53Z")
ditto ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [March 25, 2014, 1:12pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/5 "2014-03-25T13:12:00Z")
Fourthed. You definitely want a LEFT JOIN (or LEFT OUTER JOIN, which is completely identical in all ways). ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [March 25, 2014, 1:26pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/6 "2014-03-25T13:26:39Z")
Should the commentators be in a separate table? (with a table of keys relating them to their appearances on DVDs/cartoons) ---
**Author:** ![Blakeyrat](https://avatars.discourse-cdn.com/v4/letter/b/ecd19e/32.png) [@Blakeyrat](https://boards.straightdope.com/u/Blakeyrat)\ **Post date:** [March 25, 2014, 2:34pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/7 "2014-03-25T14:34:17Z")
If I were building this, I’d have 3 tables: one for DVDs, one for Cartoons, and one for Commentators. Not only does that de-duplicate the data (meaning: reduced storage, decreased query time, more effective memory caching), but it makes it a lot easier to answer questions like: “how many DVDs did episode 12 appear on?” “How many episodes did Bob Bobson comment on?” “Bob Bobson gave us a new headshot, which DVDs do we need to change the cover of?” Anyway, we don’t know how the tables are laid out based on the OP anyway. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [March 25, 2014, 2:47pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/8 "2014-03-25T14:47:57Z")
> [@Blakeyrat](#): > > If I were building this, I’d have 3 tables: one for DVDs, one for Cartoons, and one for Commentators. Not only does that de-duplicate the data (meaning: reduced storage, decreased query time, more effective memory caching), but it makes it a lot easier to answer questions like: “how many DVDs did episode 12 appear on?” “How many episodes did Bob Bobson comment on?” “Bob Bobson gave us a new headshot, which DVDs do we need to change the cover of?” > > Anyway, we don’t know how the tables are laid out based on the OP anyway. I think you’d actually need 4 (or even 5, depending on how you choose to model it) tables - 3 as you describe, and a fourth to hold all instances of a commentator on a cartoon on a DVD. Joining tables also count as tables, even if they can’t be related to a physical entity! Incidentally, normalisation doesn’t always mean decreased query time, because it increases the number of joins that have to be made. There are good arguments for denormalisation in certain large databases, but in this case the answer is clearly to go 3NF. ---
**Author:** ![dublos](https://avatars.discourse-cdn.com/v4/letter/d/f08c70/32.png) [@dublos](https://boards.straightdope.com/u/dublos)\ **Post date:** [March 25, 2014, 3:50pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/9 "2014-03-25T15:50:48Z")
Something like (Making WAG on how you connect your three tables) ```auto SELECT DVD.Name, Cartoon.Episode_Title, ISNULL( Commentator.Name, 'None') FROM DVD INNER JOIN Cartoon ON DVD.Key = Cartoon.DVD_Key LEFT JOIN Commentator ON Cartoon.Key = Commentator.Cartoon_Key ``` ---
**Author:** ![Eutychus](https://avatars.discourse-cdn.com/v4/letter/e/a3d4f5/32.png) [@Eutychus](https://boards.straightdope.com/u/Eutychus)\ **Post date:** [March 25, 2014, 4:48pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/10 "2014-03-25T16:48:47Z")
> [@Mangetout](#): > > Should the commentators be in a separate table? (with a table of keys relating them to their appearances on DVDs/cartoons) Here’s how my tables are laid out: Cartooons (titles and other info) Cartoon\_DVD\_Link (connects cartoon key to DVD key) DVD DVD\_Commentators\_Link (connects DVD key to commentators link) Commentators Here’s another issue I came across while trying to implement the OUTER JOIN. A cartoon may have commentators on it from different DVDs. So, not only does the DVD\_Commentators\_Link have to match the DVD, it also has to match the specific cartoon. Right now the way I’m doing it is backend programming where it looks for the double match (cartoon and DVD) but I was hoping there was a simpler way of doing it. ---
**Author:** ![dublos](https://avatars.discourse-cdn.com/v4/letter/d/f08c70/32.png) [@dublos](https://boards.straightdope.com/u/dublos)\ **Post date:** [March 25, 2014, 5:15pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/11 "2014-03-25T17:15:31Z")
At first I wasn’t sure why this was a complex SQL Query… your clarifications have answered that question. Normally the only reason I put an intermediate linked table into the mix if I have a many to many relationship. Is there a 6th table that is the Commentators\_Cartoon\_Link or does the DVD\_Commentators\_Link include the Cartoon key as well? I’m assuming it has both. This is venturing away from ANSI SQL to something more SQL Server specific, your flavor of DBMS may implement differently. ```auto SELECT DVD.Name, Cartoon.Episode_Title, CASE WHEN DCL.Commentator_Key IS NOT NULL THEN SELECT Commentator.Name FROM Commentator WHERE Commentator.Key = DCL.Commentator_key ELSE 'None' END AS Commentator_Name FROM Cartoon INNER JOIN Cartoon_DVD_Link CDL ON CDL.Cartoon_Key = Cartoon.Key INNER JOIN DVD ON CDL.DVD_Key = DVD.Key LEFT JOIN DVD_Commentators_Link DCL ON DCL.Cartoon_Key = Cartoon.Key AND DCL.DVD_Key = DVD.Key ``` ---
**Author:** ![Spud](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/spud/32/347_2.png) [@Spud](https://boards.straightdope.com/u/Spud)\ **Post date:** [March 25, 2014, 6:08pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/12 "2014-03-25T18:08:15Z")
To allow for the most flexibility I’d set it up using 5 tables. The basics are the DVD table with the primary identifier as DVD\_ID, the TOONS table with the primary identifier as TOON\_ID, and finally the COMMENTATOR table with the primary identifier as COM\_ID. These tables can also contain data that is unique to that item in the table. DVD could include format, date purchased, cost, etc. TOONS could have plot summary, guests, production date, etc. COMMENTATOR can have BIO, Birth/Death Dates, Photo, etc. Then I would have two link tables. DVD\_TOON link would just have the DVD\_ID and TOON\_ID. This way you can have the same cartoon on multiple DVD’s. Then you could have the DVD\_TOON\_COM link table which would connect the commentator(s) to the TOON on the Specific DVD. That setup should give you the flexibility to do pretty much whatever you need. ---
**Author:** ![Eutychus](https://avatars.discourse-cdn.com/v4/letter/e/a3d4f5/32.png) [@Eutychus](https://boards.straightdope.com/u/Eutychus)\ **Post date:** [March 25, 2014, 6:19pm UTC](https://boards.straightdope.com/t/complex-sql-query/684518/13 "2014-03-25T18:19:21Z")
Thanks for the suggestions. I’ll be playing with this when I do my work on it tomorrow. I’ll let you know how I made out.