Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
ha. I've done this far too many times already.

The answer is:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic