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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
