getting double value from group concact
- by Sackling
I am having a problem where I am getting duplicated values from what I think I should be getting.
here is my sql:
SELECT DISTINCT p.products_image,
pd.products_name,
p.products_id,
p.products_model,
p.manufacturers_id,
m.manufacturers_name,
p.products_price,
p.products_sort_order,
p.products_tax_class_id,
pd.products_viewed,
group_concat(p2i.icons_id separator ",") AS icon_ids,
group_concat(pi.icon_class separator ",") AS icon_class,
IF(s.status, s.specials_new_products_price, NULL) AS specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) AS final_price
FROM products p
LEFT JOIN specials s ON p.products_id = s.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
JOIN products_description pd ON p.products_id = pd.products_id
JOIN products_to_categories p2c ON p.products_id = p2c.products_id
INNER JOIN products_specifications ps7 ON p.products_id = ps7.products_id
LEFT JOIN products_to_icon p2i ON p.products_id = p2i.products_id
LEFT JOIN products_icons pi ON p2i.icons_id = pi.icons_id
WHERE p.products_status = '1'
AND pd.language_id = '1'
AND ps7.specification IN ('Polycotton' ,
'Reflective')
AND ps7.specifications_id = '7'
AND ps7.language_id = '1'
AND p2c.categories_id = '21'
GROUP BY p.products_id
ORDER BY p.products_sort_order
The column that is getting double values is icon_ids from the group concact. This seams to happen only if ploycotton, and reflective are both IN ps7.specification.
If it is only one or the other then it works fine.
The products_to_icon table contains 2 columns, products_id and icons_id.
If a product has 2 icons, there are 2 rows so I'm pretty sure it is this fact that is causing the duplicate icons ids.
When I run this, the icon_ids column for a product with 2 icons is "4,4,6,6" for example, when what I need is "4,6"
