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?

Similar question (with lots of ways to do this): [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) — Bukov, Feb 08 '13 at 06:53

score 55 · Accepted Answer · answered Aug 15 '10 at 08:05

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'

score 15 · Answer 2 · answered Aug 15 '10 at 08:09

15

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

answered Aug 15 '10 at 08:09

YoK

14,329
4
49
67

3

This is not without joins. The joins are just implicit. – Yay295 Jul 04 '22 at 02:27

score 4 · Answer 3 · answered Aug 15 '10 at 08:14

4

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 = ?))

answered Aug 15 '10 at 08:14

CooL i3oY

790
1
9
26

I know this seems like a different approach, but the performance on such a query, given large tables, will be *horrific*... – Adriaan Stander Aug 15 '10 at 08:15
1

No, in 90% of cases subquery better than join – CooL i3oY Aug 15 '10 at 08:23
6

And you have proof (case studies, links) of this statement? X-) – Adriaan Stander Aug 15 '10 at 08:30
1

actually both JOIN and IN approaches result in the same DB query plan - so performance-wise it should be the same – Lukáš Řádek Nov 21 '19 at 09:29

score 0 · Answer 4 · answered Aug 15 '10 at 08:05

0

Try something like

SELECT  m.*
FROM      category_product cp INNER JOIN
           product p ON cp.id_product = p.id_product INNER JOIN
           manufacturer m ON p.id_manufacturer = m.id_manufacturer
WHERE      cp.id_category = <your_value>

answered Aug 15 '10 at 08:05

Adriaan Stander

162,879
31
289
284

score -2 · Answer 5 · edited Apr 15 '19 at 14:05

-2

SELECT m.name, cp.id_category
FROM manufacturer AS M INNER JOIN product AS P
    ON M.id_manufacturer = M.id_manufacturer
 INNER JOIN category_product AS CP
    ON P.id_product = CP.id_product 
WHERE cp.id_category = 'add value'

edited Apr 15 '19 at 14:05

Rajendra Kadam

4,004
1
10
24

answered Mar 31 '19 at 07:50

Gcr

1

1

Can you format your code in a code block (4 spaces to the left of each line of code) and add some explanation for the code? – tshimkus Mar 31 '19 at 08:13
1

Thank you for your answer, however I do not think it is particularly useful to answer a 8-year old question which already has several very good answers (one of them even being accepted). – Simon Doppler Mar 31 '19 at 08:16
Especially when this is a copy of that selected answer. – Rory McCrossan Apr 10 '20 at 14:45

SQL many to many select

5 Answers5

Linked