Posted by laredotornado on 10/11/46 11:56

Hello,

How would I write a query, given these two tables and data

CATEGORIES
--------------------
CATEGORY_ID
1
2

PRODUCTS
-----------------
PRODUCT_ID CATEGORY_ID SALE_PRICE CLEARANCE_PRICE
A 1 NULL 7.50
B 1 13.50
NULL
C 2 NULL NULL
D 2 NULL 5.00

I would like to write a single MySQL query to indicate that a category
has at least one product with a non-null sale price but also return a
result for the categories that don't. Thes same logic would apply for
clearance prices. Ideally, with the above data, my result set would
look like

CATEGORY_ID HAS_SALE HAS_CLEARANCE
1 Yes Yes
2 No Yes

since categoyr 1 has product B on sale and product A on clearance, but
category 2 has no products on sale and product D on clearance. Any
ideas how I can do this? Using MySQL 4, PHP 4. Thanks, - Dave

[Back to original message]