I have a custom query which joins 3 tables related to getting a list of products and the associated categories with that product. My SQL is as follows:
SELECT products.product_id, products.name, products.price, products.enabled, cat.name
FROM products
LEFT JOIN product_categories AS pc ON products.product_id = pc.product_id
LEFT JOIN categories as cat ON pc.category_id = cat.category_id;
Now this displays almost what I want, however it shows multiple duplicate rows for the same products which have a number of associated categories. What I want to do is to get the product information for each product and all of its categories.
Am I being silly thinking that I can do this in the query or should I get my current results and process them in PHP to display the information and the categories.
SELECT products.product_id, products.name, products.price, products.enabled, cat.name
FROM products
LEFT JOIN product_categories AS pc ON products.product_id = pc.product_id
LEFT JOIN categories as cat ON pc.category_id = cat.category_id;
Now this displays almost what I want, however it shows multiple duplicate rows for the same products which have a number of associated categories. What I want to do is to get the product information for each product and all of its categories.
Am I being silly thinking that I can do this in the query or should I get my current results and process them in PHP to display the information and the categories.