CODEWITHSUNDEEP
mysqljoingroup-by
Andres SK
Andres SK

Reputation: 10974

MySQL is duplicating GROUP_CONCAT content in one of the rows

I have 3 tables:

I need a query that returns rows that contain these columns:

This is my current query, which works almost fine:

SELECT store_cat.id_cat AS id,
CONCAT(store_cat.name, IFNULL(CONCAT(": ", GROUP_CONCAT(store_cat_attribute.name ORDER BY store_cat_attribute.position SEPARATOR ", "), ""), "")) AS name,
COUNT(DISTINCT store_item.id_item) AS products
FROM store_cat
LEFT JOIN store_item ON store_item.id_cat = store_cat.id_cat
LEFT JOIN store_cat_attribute ON store_cat_attribute.id_cat = store_cat.id_cat
WHERE store_cat.id_store = 1
GROUP BY store_cat.id_cat
ORDER BY name

The problem is that in one of the rows, oddly enough, the name column is duplicating the attributes in the GROUP_CONCAT:

Category: Attribute1, Attribute1, Attribute2, Attribute2, Attribute3, Attribute3

Any ideas on why this is happening and how to solve it? Thanks!

Here you can check the sqlfiddle: http://sqlfiddle.com/#!9/7da2d3/5

Upvotes: 0

Views: 38

Answers (1)

slaakso
slaakso

Reputation: 9070

You are calculating two different things in same query (the GROUP_CONCAT and the COUNT). The JOIN to store_item will cause the duplicates. You can move to the one into the select column:

SELECT 
  store_cat.id_cat AS id, 
  CONCAT(store_cat.name, IFNULL(CONCAT(": ", 
    GROUP_CONCAT( 
       store_cat_attribute.name 
      ORDER BY store_cat_attribute.position 
      SEPARATOR ", "
    ), ""
    ), ""
   )) AS name, 
   (select count(distinct store_item.id_item) from store_item where store_item.id_cat = store_cat.id_cat) AS products 
FROM store_cat 
  LEFT JOIN store_cat_attribute ON store_cat_attribute.id_cat = store_cat.id_cat 
WHERE store_cat.id_store = 1 
GROUP BY store_cat.id_cat, store_cat.name
ORDER BY name

http://sqlfiddle.com/#!9/7da2d3/36

Upvotes: 1

Related Questions