Simpler query question 1

[panini]

hi there,

I've got 3 tables - orders1, orders2, and customers.

Should have been linked but a file overwrite has knocked the code that links them out....

I want to get a feel for how many there are that should be linked.

So i want to run a query that says - give me all the orders and customers details from orders1 and customers (linked on customerNo) where the postcode and last name match orders that are in orders2 (this is also linked on customerNo, but the systems have been duplicating customerNo's so i want to check them on postcode and last name.

I'd like to pull the details out from orders2 and customers with each line so i can see if they are matching up.

many thanks,

 

[JohnDTampaBay]

I assume the the Order2 table has LastName and PostCode columns for joining. How does this work for you? It should show you all customers, any corresponding orders from Orders1 based on CustomerNo, and any corresponding orders from Orders2 based on LastName and PostCode. But the result set could be huge. Every order in Orders2 will be duplicated for every order in Orders1.

SELECT c.*, o1.*, o2.*
FROM Customers AS c
  LEFT OUTER JOIN Orders1 AS o1
  ON c.CustomerNo = o1.CustomerNo
  LEFT OUTER JOIN Orders2 AS o2
  ON c.LastName = o2.LastName
  AND c1.PostCode = o2.PostCode

I suggest you also check for inconsistent CustomerNo's. Add WHERE c.CustomerNo <> o2.CustomerNo

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[panini]

many thanks for your help John