SQL many to many select

Question

category_product
---------------
id_category
id_product

product
---------------
id_product
id_manufacturer

manufacturer
---------------
id_manufacturer
name

How would I create an SQL query so that it selects all the names from manufacturer when id_category is equal to something?

Answer 1

It's a straightforward inner join of the tables:

SELECT m.name, cp.id_category FROM manufacturer as m INNER JOIN product as p     ON m.id_manufacturer = p.id_manufacturer INNER JOIN category_product as cp     ON p.id_product = cp.id_product WHERE cp.id_category = 'some value' 

Answer 2

Query without joins will look like following :

SELECT m.name 
FROM manufacturer as m, product as p, category_product as cp 
WHERE cp.id_category = <your value>
      AND cp.id_product = p.id_product 
      AND p.id_manufacturer = m.id_manufacturer 

Answer 3

Select M.name
From   manufacturer M
Where  M.id_manufacturer in ( Select P.id_manufacturer
                              From   product P
                              Where  P.id_product in ( Select C.id_product
                                                       From   category_product C
                                                       Where  C.id_category = ?))