Combining Multiple Inner Joins with a Left Join

[Corey McGlone]

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:

SELECT a.Field1, b.Field2, c.Field3
FROM foo a, bar b, baz c
WHERE AND a.SomeForeignKey = b.SomeForeignKey
AND a.SomeOtherForeignKey = c.SomeOtherForeignKey

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

SELECT a.Field1, b.Field2, c.Field3, d.Field4
FROM foo a, bar b, baz c
LEFT JOIN additionalTable d
ON a.YetAnotherForeignKey = d.YetAnotherForeignKey
WHERE AND a.SomeForeignKey = b.SomeForeignKey
AND a.SomeOtherForeignKey = c.SomeOtherForeignKey

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.

[Corey McGlone]

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

SELECT a.Field1, b.Field2, c.Field3, d.Field4
FROM Foo a
LEFT JOIN AdditionalTable d ON a.YetAnotherForeignKey = d.YetAnotherForeignKey
     JOIN Bar b ON a.ForeignKey = b.ForeignKey
     JOIN Baz c ON a.SomeOtherForeignKey = c.SomeOtherForeignKey

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]

Looks good Corey!

[D Rog]

Yes, it looks right.