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

Question

I have a mysql query that joins 5 different tables to return specific properties of products. It should return each product only one time. But since the product may have multiple categories it's returning the products once for each category.

Here's the select:

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)

If a given product, say product_id 32 is assigned to more than one category, it will return product_id 32 once for each category, the only difference being the result cname being the category name.

If someone could help rework the select to only include each product once, no matter the category I would appreciate the help.

It would also be helpful to have the category returned to be the MAX() category_id for the product, but not a huge deal if that's too much to ask.

Thank you.

Answer 1

If you use GROUP BY, you can get each product once irrespective of the category:

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;

For getting the MAX() category, you can try and apply this MAX function on the column that you want. I guess in this case it would be MAX(cd.name) AS cname.

For more info on GROUP BY you can go through the following manual page:

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