|
|
Posted by Juliette on 10/14/06 11:56
laredotornado@zipmail.com wrote:
> 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
>
I think the below will solve this:
SELECT distinct c.category_id as category_id, if(MAX(p1.sale_price),
"yes","no") as has_sale, if(MAX(p2.clearance_price), "yes", "no") as
has_clearance
FROM `categories` as c
LEFT JOIN `products` as p1
on c.category_id = p1.category_id and p1.sale_price IS NOT NULL
LEFT JOIN `products` as p2
on c.category_id = p2.category_id and p2.clearance_price IS NOT NULL
GROUP BY c.category_id
By the by.. just saying MySql4 is useless as 4.1 added quite some
features (such as the ability to use subqueries). Being a bit more
specific helps if you want answers which take the version you are using
into account.
Navigation:
[Reply to this message]
|