mysql - 左联接导致行重复

我有一个查询，它成功地从products表中获取唯一的产品并显示它们。我试图添加一个图标表到混合，其中一些产品可以有几个图标。问题是我试图只使用连接，例如，当一个产品有两个图标时，我将为1个产品选择2行。
以下是我的sql：

    SELECT p.products_image,
           pd.products_name,
           p.products_id,
           p.products_model,
           p.manufacturers_id,
           p.products_price,
           p2i.icons_id,
           p.products_tax_class_id,
           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 manufacturers m
                  ON p.manufacturers_id = m.manufacturers_id
           LEFT JOIN specials s
                  ON p.products_id = s.products_id
           LEFT JOIN products_to_categories p2c
                  ON p.products_id = p2c.products_id
           LEFT JOIN products_description pd
                  ON p.products_id = pd.products_id
           LEFT JOIN products_to_icon p2i
                  ON p.products_id = p2i.products_id
    WHERE  p.products_status = '1'
           AND pd.language_id = '1'
           AND p2c.categories_id = '36'

最佳答案

在末尾添加一个GROUP BY子句。
如果仍然需要所有图标ID，可以与group_concat
例如。：

SELECT p.products_image,
       pd.products_name,
       p.products_id,
       group_concat(p2i.icons_id separator ",") as icons_ids
       (...)
WHERE  p.products_status = '1'
       AND pd.language_id = '1'
       AND p2c.categories_id = '36'
GROUP BY p.products_id

关于mysql - 左联接导致行重复，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11351966/