p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   Finding who doesn't sell to a certain place... (http://p2p.wrox.com/showthread.php?t=81985)

03jh01 December 21st, 2010 07:21 PM

Finding who doesn't sell to a certain place...
 
Hi all,

I have a Vendor table which links to an Order table via VendorID. I have a Customer table that also links to the Order table via CustomerID.

This means the order table provides a list of all orders, and the IDs of the Customers who made the orders, and the Vendors who handled the orders. For a given order, I can find the State that the customer is from (using Customer table's State field).

This means I can get where the Vendor's sell too. I need to use a sql query to produce a list of vendor's that have NEVER sold to a customer in New York. How do I do this? I just cannot get my head around it.

Thanks.

Old Pedant December 21st, 2010 08:16 PM

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.

03jh01 December 22nd, 2010 05:19 AM

Thank you very much for the reply. It was very helpful!

And yes, would you mind giving an Access solution as well please, if only for my own future reference?

I also want to be able to find all Vendors that have only ever sold to New York , and nowhere else. I'm guessing it would be a similar query wouldn't it? It is basically just the opposite of finding Vendors that have NEVER sold to New York, isn't it?

Also, this part:

Code:

SELECT DISTINCT O.VendorID
FROM Customers

Will that work if the Customers table doesn't have a VendorID field, as the Customers table doesn't have a VendorID?

Thank you very much once again :).

Old Pedant December 22nd, 2010 03:29 PM

Read the *FULL* sub-query:
Code:

    SELECT DISTINCT O.VendorID
    FROM Customers AS C INNER JOIN Orders AS O
    ON ( O.customerID=C.customerID AND C.State='NY' )

The vendorID is *NOT* coming from the Customers table...it's coming from the Orders table. Perhaps it would have been less confusing if I had written that at
Code:

    SELECT DISTINCT O.VendorID
    FROM Orders AS O INNER JOIN Customers AS C
    ON ( O.customerID=C.customerID AND C.State='NY' )

but the order the tables appear is not important for an INNER join.

****************

Access almost surely can't handle that query, as is, because it would be too complex for its tiny brain.

But what you can do is take that inner query (the one posted just above in *this* message) and STORE it in Access. That is, bring up Access, create a new query in design mode, SQL view. Then save the query under an easy to remember name. Say you save it as "NYVendors".

Then you simply USE that query AS IF IT IS A TABLE in the main query, thus:
Code:

SELECT vendorName
FROM Vendors 
WHERE vendorID NOT IN (  SELECT VendorID FROM NYVendors )
ORDER BY vendorName


Old Pedant December 22nd, 2010 03:38 PM

Quote:

I also want to be able to find all Vendors that have only ever sold to New York , and nowhere else.
That's tougher. Hmmm...
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 = 'NY' )
AND vendorID NOT IN (
    SELECT O.VendorID
    FROM Orders AS O, Customers AS C
    WHERE O.customerID = C.customerID AND C.State <> 'NY' )

I think that will do it.

Old Pedant December 22nd, 2010 03:40 PM

CAUTION: None of these queries are going to be very efficient unless you have the right indexes in place!! What are "right indexes"? It depends on the DB. MySQL is especially fussy about indexes for something like this.

03jh01 December 22nd, 2010 03:42 PM

Hello again.


Thanks for your help again. I am having issues with both queries (no doubt it is something I am doing :)). I am using MySQL and I cannot get them to run.

With the first one, I get an error saying I have a syntax error.

With the second one, I get an error 'unknown column 'Order.VendorID''. I know for a fact that that column does exist in that table however.

Any ideas what I may be doing wrong?

Thanks again.

Old Pedant December 22nd, 2010 05:12 PM

Once you give an alias to a table (e.g, "Orders AS O") you can no longer use the full table name to refer to fields.

So if the code does "Orders AS O" then you need to use "O.VendorID"

For the "'Every derived table must have its own alias": Show exactly the query you are now using.

03jh01 December 22nd, 2010 05:46 PM

I do apologise...those queries are flawless. They work perfectly! It was just me being an idiot.

I have one more query I would like to do. I would like to get all vendors that sell to California (for example), but do not sell to New York.

How long would you say it takes to become as efficient as your very clearly are in SQL, if you don't mind me asking?

Also, I would probably like to ask you a few specific questions about how parts your queries work sometime in the near future, just so I fully understand as I would like to learn SQL properly, if that's alright? I think your knowledge and experience would be invaluable to me.

Old Pedant December 22nd, 2010 06:24 PM

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.


All times are GMT -4. The time now is 02:18 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.