I think that will give me one record for every price that is not Yen, so if a product has a price in USD and a price in GBP it will show up twice. A GROUP BY might help, I'll have to chew on that. It seems too simple. 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
_____ From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, July 12, 2007 12:05 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: SELECT missing records Try this SELECT prod.prod_num, price.prod_price FROM prod JOIN price WHERE prod.prod_id = price.prod_id AND price.prod_curr !='YEN'; On 7/12/07, Jerry Schwartz <[EMAIL PROTECTED]> wrote: 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/ <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]
