File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
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:
 
Consider Paul's rocket mass heater.
 
subject: using LEFT JOIN to join 1 to N tables - Howto?
 
Similar Threads
combining LEFT OUTER JOIN and WHERE
Hibernate SchemaUpdate problem
Hibernate is driving me crazy !
Restore problem
Mapping ENUM('0', '1') or CHAR(0) for Boolean