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.
SHOW CREATE TABLE tablename;
and sample data. Better use SQLFiddle for this. – Alexander