Ron: Especially in SQL Server, I'm thinking using the "ON" join syntax is a better choice than trying to put the join condition in the WHERE clause. It's a lot more flexible, as you discovered, and as a very valuable side benefit it's generally a lot faster, too. Something to do with the optimization plan SQL Server comes up with to run the query. With that in mind, does the following query give you the same results as with your query? SELECT a buncha junk FROM Table_1 AL1 LEFT OUTER JOIN Customer AL4 ON AL1.CUSTOMER_NUMBER=AL4.CUSTOMER_NUMBER INNER JOIN Table_2 AL2 ON AL1.PART=AL2.Sku INNER JOIN table_3 AL3 ON AL1.Code=AL3.CODE WHERE (other conditions) If you wanted to be very archaic, I think you could also use the "*=" syntax to force a left outer join when you had the join conditions in the WHERE clause, e.g.: SELECT a buncha junk FROM Table_1 AL1, Table_2 AL2, table_3 AL3, Customer AL4 WHERE AL1.PART=AL2.Sku AND AL1.Code=AL3.CODE AND AL1.CUSTOMER_NUMBER *= AL4.CUSTOMER_NUMBER AND (other conditions) Steve Bigelow TKCSB Images -----Original Message----- From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Luther, Ron Sent: Monday, May 09, 2011 12:37 PM To: thelist at lists.evolt.org Subject: Re: [thelist] MS Table Join Question (from Excel) The redoubtable RonL. asked such a question: >>Select a buncha junk >>FROM Table_1 AL1, Table_2 AL2, table_3 AL3, Customer AL4 WHERE >>(AL1.PART=AL2.Sku AND AL1.Code=AL3.CODE AND >>AL4.CUSTOMER_NUMBER=AL1.CUSTOMER_NUMBER) >>AND (other conditions) >>The problem is that this AL4 customer reference table may not be complete ... so I need a >>leftjoin/rightjoin on that last bit in the where clause to pull in all records from table AL1 regardless of >>whether they match up in AL4 or not. >>Can I buy a clue on the syntax? And found his own answer! (Well, technically, I cheated and created the answer using a separate gui based reporting technology that allowed me to peek under the skirt and check out the answer!) Which is ... Select a buncha junk FROM Table_2 AL2, table_3 AL3, Table_1 AL1 LEFT OUTER JOIN Customer AL4 ON (AL1.CUSTOMER_NUMBER=AL4.CUSTOMER_NUMBER) WHERE (AL1.PART=AL2.Sku AND AL1.Code=AL3.CODE) AND (other conditions) Didn't know about that "on" splifflety goodness. Sorted! Cheers, RonL. -- * * Please support the community that supports you. * * http://evolt.org/help_support_evolt/ For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt !