查询返回基于类别的重复产品

Question

我有一个mysql查询，它连接5个不同的表以返回产品的特定属性。它应该只返回每个产品一次。但是由于产品可能有多个类别，它为每个类别返回产品一次。

以下是选择：

SELECT DISTINCT 
       pd.name AS pname, 
       p.price AS price, 
       cd.name AS cname, 
       pd.description AS pdescription, 
       pd.language_id AS language_id, 
       pd.product_id AS product_id, 
       p.model AS model, 
       p.sku AS sku, 
       p.upc AS upc, 
       m.name AS brand 
  FROM {$this->prefix}product_description pd 
       LEFT JOIN {$this->prefix}product_to_category pc 
                 ON (pd.product_id = pc.product_id) 
       INNER JOIN {$this->prefix}product p 
                 ON (pd.product_id = p.product_id) 
       LEFT JOIN {$this->prefix}category_description cd 
                 ON (cd.category_id = pc.category_id 
                     AND cd.language_id = pd.language_id) 
       LEFT JOIN {$this->prefix}manufacturer m 
                 ON (m.manufacturer_id = p.manufacturer_id)

如果给定的产品，比如product_id32被分配到多个类别，它将为每个类别返回一次product_id32，唯一的区别是结果cname是类别名称。

如果有人可以帮助返工选择只包括每个产品一次，无论类别我将不胜感激的帮助。

将类别恢复为产品的MAX（）category_id也会很有帮助，但如果这太过分了，那就没什么大不了的了。

谢谢你。

Answer 1

很可能是因为您使用了product_to_category和产品之间的内部联接

从本质上讲，内部联接将创建尽可能多的连接，其中左联接将生成的结果限制在第一个（左）表中的元素。

Answer 2

如果使用“分组依据”,则无论产品类别如何，您都可以获得一次产品:

SELECT DISTINCT 
pd.name AS pname, 
p.price AS price, 
cd.name AS cname, 
pd.description AS pdescription, 
pd.language_id AS language_id, 
pd.product_id AS product_id, 
p.model AS model, 
p.sku AS sku, 
p.upc AS upc, 
m.name AS brand 
FROM {$this->prefix}product_description pd 
LEFT JOIN {$this->prefix}product_to_category pc 
ON (pd.product_id = pc.product_id) 
INNER JOIN {$this->prefix}product p 
ON (pd.product_id = p.product_id) 
LEFT JOIN {$this->prefix}category_description cd 
ON (cd.category_id = pc.category_id AND cd.language_id = pd.language_id) 
LEFT JOIN {$this->prefix}manufacturer m 
ON (m.manufacturer_id = p.manufacturer_id)
GROUP BY product_id;

为了获得MAX（）类别，可以尝试将此MAX函数应用于所需的列。我猜在这种情况下，它将是MAX（cd.name）作为cname。

有关GROUP BY的更多信息，您可以浏览以下手册页面：

https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html