Mysql query different group by
Hello i have a products table that contains normal products and configurable product
It has a basic stucture of:
- id
- name
- price
- configurable ('yes', 'no')
- id_configuration
Normal products have configurable no and 0 as id configuration, and configurable products have it set to yes and have the same id_configuration value.
The current query is:
SELECT `products`.*
FROM `products`, `categories`, `product_categories`
WHERE `categories`.`id` = 23 AND
`products`.`id` = `product_categories`.`id_product` AND
`categories`.`id` = `product_categories`.`id_category` AND
`products`.`active` = 'yes'
ORDER BY `pos_new` ASC, `created` DESC LIMIT 0,20
I was wondering if there is a way to group by id_configuration, but only for the configurable products. The reason is that i want only one of the configuration products to show in search. I was thinking i could do a join, but was wondering if there is a way to do some kind of special group by. For example for configurable yes the field should be id_configuration otherwise it should be the id field
Tha开发者_如何学Cnks a lot for any sugestions
You can use a CASE statement in the GROUP BY clause, and group by id_configuration if configurable is 'yes', or by id, which is unique (and therefore the equivalent of not grouping) if not.
SELECT * FROM `products`
GROUP BY CASE `configurable` WHEN 'yes' THEN `id_configuration` ELSE `id` END
精彩评论