CODEWITHSUNDEEP
mysqldatabasediagram
user577732
user577732

Reputation: 4056

Reading a database diagram

I've been trying to figure out how to get the products that match a certain category id but I have been unable to figure out how to move from category to products.

How would a query that basically selects all products that match a certain category id look?

enter image description here

Upvotes: 0

Views: 625

Answers (1)

Beat
Beat

Reputation: 1380

This should work:

SELECT products.*
FROM products,
     product_category
WHERE product_category.categoryid = CATEGORY_ID
  AND products.catalogid = product_category.catalogid

Or if you prefer a join:

SELECT products.*
FROM products
INNER JOIN product_category ON products.catalogid = product_category.catalogid
WHERE product_category.categoryid = CATEGORY_ID

Simply replace CATEGORY_ID by the ID of the category you wish to select.

product_category is a link table, joining the tables products and product_category together: it contains the catalogid, referencing the ID of the category, and catalogid, referencing the ID of the product.

Upvotes: 1

Related Questions