Mysql query different group by

Question

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

Thanks a lot for any sugestions

Answer 1

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