• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

using LEFT JOIN to join 1 to N tables - Howto?

 
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Say I have something like:


What I really care about is, I want a result of all prizes, regardless of whether a winner has been assigned.


Now how do I retrieve the other 'named' values for the other foreign keys in 'prize' ?
Neither of the following work:

Here's what doesn't generate an error, but what *does* happen is that rows are dropped out,
when there are no matching rows in the sponsor and location tables. (which should never happen,
but if it *does*, i still want the query to return unmatched rows).


So that last one doesn't fail, but it also drops rows, which was the whole point of using LEFT JOIN (except
I can't seem to figure out how to get 1->N joins with LEFT JOIN).

Help!

p.s. MySql, so no subqueries!
 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ha. I've done this far too many times already.

The answer is:
 
WHAT is your favorite color? Blue, no yellow, ahhhhhhh! Tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic