CODEWITHSUNDEEP
mysqlgroup-by
Gabriel Solomon
Gabriel Solomon

Reputation: 30035

Mysql query different group by

Hello i have a products table that contains normal products and configurable product

It has a basic stucture of:

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

Upvotes: 0

Views: 247

Answers (1)

Vic
Vic

Reputation: 12527

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

Upvotes: 1

Related Questions