View Single Post
  #10 (permalink)  
Old December 22nd, 2010, 06:24 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

Quote:
I would like to get all vendors that sell to California (for example), but do not sell to New York.
Trivial change to prior query:
Code:
SELECT vendorName 
FROM Vendors 
WHERE vendorID IN (
      SELECT O.VendorID
      FROM Orders AS O, Customers AS C
      WHERE O.customerID = C.customerID AND C.State = 'CA' ) 
AND vendorID NOT IN (
      SELECT O.VendorID
      FROM Orders AS O, Customers AS C
      WHERE O.customerID = C.customerID AND C.State = 'NY' )
Look, the only important part here are those subqueries. So let's look at one carefully:
Code:
      SELECT O.VendorID
      FROM Orders AS O, Customers AS C
      WHERE O.customerID = C.customerID AND C.State = 'CA'
Take it backwards.

First, we want to find all customers in CAlifornia. So that is simply
Code:
     FROM ... Customers AS C
     WHERE .... C.State = 'CA'
Note that if we have only *THOSE* records, the clearly the values in C.CustomerID for them are the id's of the customers in CAlifornia, right?

So now we just have to find the records in the Orders table that match those customers. That is, where Orders.CustomerID = Customers.CustomerID

Or, more fully
Code:
      FROM Orders AS O, Customers AS C
       WHERE O.customerID = C.customerID AND C.State = 'CA'
Note that this is *100% EQUIVALENT* to using
Code:
      FROM Orders AS O INNER JOIN Customers AS C
       ON ( O.customerID = C.customerID AND C.State = 'CA' )
Joining two tables using a WHERE clause is what I refer to as an "IMPLICIT inner join", as opposed to an explicit INNER JOIN. There's no difference in the semantics or performance. It's simply a syntax difference. [Same does not apply to LEFT or RIGHT joins...those must be explicit.]

So if doing
Code:
      FROM Orders AS O, Customers AS C
        WHERE O.customerID = C.customerID AND C.State = 'CA'
gives us all the Orders records for all customers whose state is CAlifornia, then all we have to do is add the SELECT to choose which field (or fields...but we only want one in this case) we need:
Code:
      SELECT O.VendorID
       FROM Orders AS O, Customers AS C
       WHERE O.customerID = C.customerID AND C.State = 'CA'
And then, finally, we use this list of VendorID's (because that's what it really is, now: just a list of IDs) to either select WHERE VendorID IN (xxx) or *de*select WHERE VendorID NOT IN (xxx) vendors from all the possible vendors.

These are really pretty simply queries, as SQL goes. I've got some queries that literally take 4 and 5 pages of paper to print out. I've been using SQL off and on for 20 years now, but I've been programming for almost 40 years, and it all tends to be related. But I have a young friend who has concentrated on SQL and is more proficient than I am in it, and he's only been at it maybe 3 years, tops.
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
03jh01 (December 22nd, 2010)