[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