Have Geek Brain

MySQL Join question

Hi I am trying to write a specific MySQL join query.

I have a table that contains product data, each product can belong to multiple categories. This m: m relationship is done using a table of links.

For this specific query, I want to get all the products that belong to a given category, but with each product record, I also want to return the other categories the product belongs to.

Ideally, I would like to achieve this by using an Inner Join on the category table, instead of doing an extra query for each product record, which would be quite inefficient.

My simplified diagram is designed something like this:

products table:

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

category table:

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

product_category:

product_id, category_id

I wrote the following query which allows me to get all products belonging to a specified category_id. However, I am really trying to work out how to get the other categories the 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 would be happy to just have a category ID issued for each row of the returned product, since I will have all the category data stored in an object and my application code can take care of the rest.

Many thanks,

Richard

+2


a source to share


2 answers 


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


+1


a source


You would use an inner join on the product_category table, making a left join is pointless since you are using the value from it in the state. Then you make a left join on the product_category table to get other categories and join 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
0


a source






More articles:


All Articles