[thelist] MS Table Join Question (from Excel)

Steve Bigelow steve.bigelow at tkcsb.com
Wed May 11 19:53:22 CDT 2011


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 ! 



More information about the thelist mailing list