aspose file tools*
The moose likes JDBC and the fly likes Combining Multiple Inner Joins with a Left Join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Combining Multiple Inner Joins with a Left Join" Watch "Combining Multiple Inner Joins with a Left Join" New topic
Author

Combining Multiple Inner Joins with a Left Join

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
So I'm trying to write a query which is going to combine values from 4 tables. Three of those tables will be combined using Inner Joins. However, the fourth table must be combined used a left join as there may or may not be an index for a record in that table. Here's my query (well, a simplified version of it, anyway) using only inner joins:



What I'd like to do is something similar to the following:



Unfortunately, that doesn't seem to be valid SQL and I'm no SQL expert to figure out the best way to go about this.

The trick here is that a.YetAnotherForeignKey may be null. If it is not null, I want to pull the information from "additionalTable". If it is, that's just fine.

Anyone have a good way to do this in a single query? I can handle inner joins and I can handle left joins, but I can't say I've ever tried to put the two together quite like this.

Thanks.


SCJP Tipline, etc.
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Sorry for the trouble, but I think I may have actually worked out a solution. Using a different syntax, I came up with this:



That seems to be giving me the data that I want. Anyone see any issues with that query, or am I in the clear?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

Looks good Corey!


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

Yes, it looks right.


Retire your iPod and start with HD Android music player Kamerton | Minimal J2EE container is here | Light weight full J2EE stack | and build tool | Co-author of "Windows programming in Turbo Pascal"
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Combining Multiple Inner Joins with a Left Join