Java MySQL | retrieving matches from many-to-many groupings

Question

Considering three tables in MySQL:

product  
id - name - (more columns, irrelevant) ... 
13  - chair  
64  - table  

method  
id - name  
 1 - welding  
 2 - stamping  
10 - forging

product-method (many-to-many)  
product_id - method_id 
13 -  1  
13 -  2  
13 - 10  
64 -  1

Basically, what I need is a list of methods that are linked to a specific product.
In this example, I would like to get all methods that are used to make a chair (product_id 13 in product-method). The returned list should then be:

welding  
stamping  
forging 

I began with this query:

SELECT method_id  
FROM `product-method`  
WHERE `product_id` = 13 

which returns 1, 2 and 10

I tried this query to extract the method's names from the returned method_id's:

SELECT name  
FROM `product-method`  
WHERE id = (SELECT method_id FROM `product-method` WHERE product_id = 13)

which returns nothing.

What query should I use?

Answer 1

Try this:

SELECT name
FROM method
WHERE id IN
  (SELECT method_id FROM product_method WHERE product_id = 13
  )

it gets the applicable method_ids for given product_id from product_method table and then finds the name of the method from the method table using IN in the WHERE clause.

Alternatively, you can use JOIN like this:

Select m.*
From method m join product_method pm
On m.id = pm.method_id
Where pm.product_id = 13;