Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 21st, 2010, 07:21 PM
Registered User
 
Join Date: Dec 2010
Posts: 5
Thanks: 7
Thanked 0 Times in 0 Posts
Default 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.

Last edited by 03jh01; December 22nd, 2010 at 01:20 PM..
Reply With Quote
  #2 (permalink)  
Old December 21st, 2010, 08:16 PM
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)
  #3 (permalink)  
Old December 22nd, 2010, 05:19 AM
Registered User
 
Join Date: Dec 2010
Posts: 5
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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 :).

Last edited by 03jh01; December 22nd, 2010 at 01:27 PM..
Reply With Quote
  #4 (permalink)  
Old December 22nd, 2010, 03:29 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
03jh01 (December 22nd, 2010)
  #5 (permalink)  
Old December 22nd, 2010, 03:38 PM
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 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.
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
03jh01 (December 22nd, 2010)
  #6 (permalink)  
Old December 22nd, 2010, 03:40 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
03jh01 (December 22nd, 2010)
  #7 (permalink)  
Old December 22nd, 2010, 03:42 PM
Registered User
 
Join Date: Dec 2010
Posts: 5
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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.

Last edited by 03jh01; December 22nd, 2010 at 05:09 PM..
Reply With Quote
  #8 (permalink)  
Old December 22nd, 2010, 05:12 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
03jh01 (December 22nd, 2010)
  #9 (permalink)  
Old December 22nd, 2010, 05:46 PM
Registered User
 
Join Date: Dec 2010
Posts: 5
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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.

Last edited by 03jh01; December 22nd, 2010 at 05:56 PM..
Reply With Quote
  #10 (permalink)  
Old December 22nd, 2010, 06:24 PM
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)
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Which storage engine for "Post for sell goods" website taev MySQL 2 May 8th, 2009 02:53 PM
Sell more books Paulpdy BOOK: PHP and MySQL: Create-Modify-Reuse ISBN: 978-0-470-19242-9 6 February 5th, 2009 05:30 PM
who can help me to sell software laolai12 Flash (all versions) 0 June 18th, 2008 03:38 AM
cheap brand shoes sell saler VBScript 0 November 26th, 2007 07:12 AM
How to Sell PRODUCTS in the own site? Salar Beginning PHP 1 August 3rd, 2005 02:26 PM



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


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