GeeCON Prague 2014*
The moose likes JDBC and the fly likes using LEFT JOIN to join 1 to N tables - Howto? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "using LEFT JOIN to join 1 to N tables - Howto?" Watch "using LEFT JOIN to join 1 to N tables - Howto?" New topic
Author

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

Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

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

Joined: Feb 20, 2001
Posts: 3695

ha. I've done this far too many times already.

The answer is:
 
GeeCON Prague 2014
 
subject: using LEFT JOIN to join 1 to N tables - Howto?