MySQL Join Question

Question

Hi i'm struggling to write a particular MySQL Join Query.

I have a table containing product data, each product can belong to multiple categories. This m:m relationship is satisfied using a link table.

For this particular query I wish to retrieve all products belonging to a given category, but with each product record, I also want to return the other categories that product belongs to.

Ideally I would like to achieve this using an Inner Join on the categories table, rather than performing an additional query for each product record, which would be quite inefficient.

My simplifed schema is designed roughly as follows:

products table:

product_id, name, title, description, is_active, date_added, publish_date, etc....

categories table:

category_id, name, title, description, etc...

product_category table:

product_id, category_id

I have written the following query, which allows me to retrieve all the products belonging to the specified category_id. However, i'm really struggling to work out how to retrieve the other categories a product belongs to.

SELECT p.product_id, p.name, p.title, p.description
FROM prod_products AS p
LEFT JOIN prod_product_category AS pc
ON pc.product_id =  p.product_id
WHERE pc.category_id = $category_id
AND UNIX_TIMESTAMP(p.publish_date) < UNIX_TIMESTAMP()
AND p.is_active = 1
ORDER BY p.name ASC

I'd be happy just retrieving the category id's releated to each returned product row, as I will have all category data stored in an object, and my application code can take care of the rest.

Many thanks,

Richard

Answer 1

SELECT p.product_id, p.name, p.title, p.description, 
  GROUP_CONCAT(otherc.category_id) AS other_categories
FROM prod_products AS p
JOIN prod_product_category AS pc
  ON pc.product_id =  p.product_id
LEFT JOIN prod_product_category AS otherc
  ON otherc.product_id = p.product_id AND otherc.category_id != pc.category_id
WHERE pc.category_id = $category_id
  AND UNIX_TIMESTAMP(p.publish_date) < UNIX_TIMESTAMP()
  AND p.is_active = 1
GROUP BY p.product_id
ORDER BY p.name ASC

Answer 2

You would use an inner join to the product_category table, doing a left join there is pointless as you are using the value from it in the condition. Then you do a left join on the product_category table to get the other categories, and join in the categories for the data:

select
  p.product_id, p.name, p.title, p.description,
  c.category_id, c.name, c.title
from
  prod_products p
  inner join prod_product_category pc on pc.product_id = p.product_id
  left join prod_product_category pc2 on pc2.product_id = p.product_id
  left join prod_categories c on c.category_id = pc2.category_id
where
  pc.category_id = @category_id and
  unix_timestamp(p.publish_date) < unix_timestamp() and
  p.is_active = 1
order by
  p.name