On 7/12/07, Jerry Schwartz <[EMAIL PROTECTED]> wrote:
I believe this query
will do it, but can it be redone without the sub-query by using JOINs?
Yes, use a LEFT JOIN.
Would that be more efficient?
Yes.
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");
I don't think this does what you want. You said you wanted every
product with a USD price but no YEN price. This one does that:
SELECT prod.prod_num, usd_price.prod_price
FROM prod
JOIN price usd_price ON (prod.prod_id = usd_price.prod_id
AND usd_price.prod_curr = 'USD')
LEFT JOIN price yen_price ON (prod.prod_id = yen_price.prod_id
AND yen_price.prod_curr = 'YEN')
WHERE yen_price.prod_id IS NULL
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
