CODEWITHSUNDEEP
mysql
Shah Rukh
Shah Rukh

Reputation: 300

Why is SQL GROUP_CONCAT Query not working?

I have this query, but it gives me duplicate values in row

select products.name, products.image, products.image_second, 
products.description, products.min_order, size_categories.size_id, 
products.id, 
GROUP_CONCAT( product_prices.price order by FIND_IN_SET(sizes.id, size_categories.size_id)) as price,
GROUP_CONCAT( sizes.name order by FIND_IN_SET(sizes.id, size_categories.size_id) ) as sizes_name, 
units.name as units_name 
from `products` 
inner join `subcategories` on `products`.`subcategories_id` = `subcategories`.`id` 
inner join `size_categories` on `subcategories`.`categories_id` = `size_categories`.`categories_id` 
inner join `sizes` on FIND_IN_SET(sizes.id, size_categories.size_id) > '0' 
inner join `units` on `units`.`id` = `products`.`units_id` 
inner join product_prices on product_prices.products_id = products.id 
where `products`.`id` = '1' 
group by `products`.`name`, `products`.`image`, 
`products`.`image_second`, 
`products`.`description`, `products`.`min_order`, 
`size_categories`.`size_id`, `products`.`id`

The result is like this

------ size_id | id | price          | sizes_name
------  1,2       1   43,32,43,32       2m,2m,3m,3m

32 is the price of 2m, and 43 is the price of 3m. I need it in a single row and also i need to maintain the order (it should be like 32,43 and not like 43,32) like

------ size_id | id | price | sizes_name
------  1,2       1   32,43     2m,3m

Please Help

Upvotes: 1

Views: 180

Answers (1)

forpas
forpas

Reputation: 164204

You missed the condition:

...and product_prices.size_id = sizes.id

when you join product_prices.
With this condition you don't need DISTINCT inside GROUP_CONCAT() for this sample data, although it may be needed for your actual data:

select products.name, size_categories.size_id, products.id, 
GROUP_CONCAT(product_prices.price order by FIND_IN_SET(sizes.id, size_categories.size_id)) as price,
GROUP_CONCAT(sizes.name order by FIND_IN_SET(sizes.id, size_categories.size_id)) as sizes_name 
from products 
inner join subcategories on products.subcategories_id = subcategories.id 
inner join size_categories on subcategories.categories_id = size_categories.categories_id 
inner join sizes on FIND_IN_SET(sizes.id, size_categories.size_id)
inner join product_prices on product_prices.products_id = products.id and product_prices.size_id = sizes.id 
where products.id = '1' 
group by products.name, size_categories.size_id, products.id;

See the demo.
Results:

> name | size_id | id | price | sizes_name
> :--- | :------ | -: | :---- | :---------
> PR1  | 1,2     |  1 | 32,43 | 2m,3m

Upvotes: 1

Related Questions