getting double value from group concact

Posted by Sackling on Stack Overflow See other posts from Stack Overflow or by Sackling
Published on 2013-10-28T03:10:24Z Indexed on 2013/10/28 3:54 UTC
Read the original article Hit count: 129

Filed under:

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"

© Stack Overflow or respective owner

Related posts about sql