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:anandkl@gmail.com]
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 <jschwartz@the-infoshop.com> 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/mysql?unsub=anandkl@gmail.com