List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:July 12 2007 3:55pm
Subject:SELECT missing records
View as plain text  
I've been banging my head against the walls for hours, so I hope somebody
can help. I know similar questions have been answered in the past.

I have two tables, prod and price. Stripping out the non-essential fields,
they are pretty simple:

prod
-------
prod_num (int)
prod_id (char 15)

price
-----
prod_price_id (char 15)
prod_id (char 15)
prod_price (decimal 10,2)
prod_curr (varchar 10)

Here's what I need to do:

Find every prod.prod_num that has a corresponding price.prod_curr = "USD"
but does NOT have a corresponding price.prod_curr = "YEN".

A product might have a price row with price.prod_curr = "GBP" or some other
currency, so a product might 1, 2, 3, or more prices. I believe this query
will do it, but can it be redone without the sub-query by using JOINs? Would
that be more efficient?

SELECT prod.prod_num, price.prod_price
FROM prod JOIN price
WHERE prod.prod_id = price.prod_id
AND price.prod_id NOT IN
(SELECT price.prod_id FROM price
 WHERE price.prod_curr = "YEN");

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com <http://www.the-infoshop.com/>
www.giiexpress.com <http://www.giiexpress.com/>
www.etudes-marche.com



Thread
SELECT missing recordsJerry Schwartz12 Jul
  • Re: SELECT missing recordsAnanda Kumar12 Jul
    • RE: SELECT missing recordsJerry Schwartz12 Jul
      • Re: SELECT missing recordsPerrin Harkins13 Jul
  • Re: SELECT missing recordsPerrin Harkins12 Jul
    • RE: SELECT missing recordsJerry Schwartz12 Jul
      • Re: SELECT missing recordsPerrin Harkins13 Jul
        • Re: SELECT missing recordsmos13 Jul
          • Re: SELECT missing recordsPerrin Harkins13 Jul
    • RE: SELECT missing recordsJerry Schwartz12 Jul