View Single Post
  #2 (permalink)  
Old December 21st, 2010, 08:16 PM
Old Pedant Old Pedant is offline
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You don't say what DB you are using, but I think this query should work in most anything but poor little Access:
Code:
SELECT V.vendorName
FROM Vendors AS V
LEFT JOIN ( 
    SELECT DISTINCT O.VendorID
    FROM Customers AS C INNER JOIN Orders AS O 
        ON ( O.customerID=C.customerID AND C.State='NY' ) 
   ) AS X
ON V.vendorID = X.vendorID
WHERE X.vendorID IS NULL
ORDER BY V.vendorName
Or you could probably do this:
[code]
Code:
SELECT vendorName
FROM Vendors 
WHERE vendorID NOT IN (
    SELECT DISTINCT O.VendorID
    FROM Customers AS C INNER JOIN Orders AS O 
        ON ( O.customerID=C.customerID AND C.State='NY' ) 
   ) 
ORDER BY vendorName
The DISTINCT isn't needed in that one, but probably can't hurt. I suspect the LEFT JOIN will be more efficient. But depends on what DB you are using.

If you need an answer for Access, ask.
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
03jh01 (December 22nd, 2010)