SQL Help With Join

[Auguy]

I'm trying to do this join in the best way.

I've started with this code to select some orders and all works well. The syntax may not be exactly correct, just trying to get the concepts right.

SELECT OrderMaster.OrderPK, OrderMaster.CustomerFK,
OrderMaster.DateReceive, OrderMaster.DateShip, OrderMaster.DateRequest, OrderMaster.DateFinish,
OrderMaster.Shipper, OrderMaster.PO,
OrderMaster.OrderPrinted, Notes,
Customer.Account, Customer.CustName
FROM OrderMaster
LEFT OUTER JOIN Customer ON OrderMaster.CustomerFK = Customer.CustomerPK
Where (some order date restrictions here)

Now I only want Orders that have specific part numbers and I don't want the order listed more than once because the part number may be on the order multiple times.

Should I do something like this?

SELECT Distinct OrderMaster.OrderPK, OrderMaster.CustomerFK,
OrderMaster.DateReceive, OrderMaster.DateShip, OrderMaster.DateRequest, OrderMaster.DateFinish,
OrderMaster.Shipper, OrderMaster.PO,
OrderMaster.OrderPrinted, Notes,
Customer.Account, Customer.CustName
FROM OrderMaster
LEFT OUTER JOIN Customer ON OrderMaster.CustomerFK = Customer.CustomerPK
JOIN OrderDetail ON OrderMaster.OrderPK = OrderDetail.OrderFK And OrderDetail.PartNo Like 'PART123%'
Where (some order date restrictions here)

I'm thinking this is not a good use of Distinct.

Or, should I get just the distinct OrderPK's that have the part # and then join with the OderMaster to get all of the fields I need?


Auguy
Northwest Ohio

 

[r937]

SELECT OrderMaster.OrderPK
     , OrderMaster.CustomerFK
     , OrderMaster.DateReceive
     , OrderMaster.DateShip
     , OrderMaster.DateRequest
     , OrderMaster.DateFinish
     , OrderMaster.Shipper
     , OrderMaster.PO
     , OrderMaster.OrderPrinted
     , Notes
     , Customer.Account
     , Customer.CustName   
  FROM OrderMaster
INNER
  JOIN Customer 
    ON Customer.CustomerPK = OrderMaster.CustomerFK
 WHERE /* some order date restrictions here */
   AND EXISTS
       ( SELECT 1
           FROM OrderDetail 
          WHERE OrderFK = OrderMaster.OrderPK
            AND PartNo LIKE 'PART123%' )

note i changed your LEFT OUTER JOIN to Customer to an INNER JOIN

i assume you don't have any orders for customers that don't exist!!!

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Auguy]

Thanks for the quick response. The outer join was in there because the data was converted from another system and I wanted to see if there were any orphans.

Auguy
Northwest Ohio