Reading a database diagram

Question

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?

Answer 1

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.