Select statement using WHERE x IN (SELECT ...)

Question

I have two tables:

[ product_to_category
---------------------
product_id
category_id


[ category
---------------------
category_id
parent_id

I need to get all product_id's with a category.parent_id of '39'. Here's what I'm trying, but it's returning empty when there are at least a few hundred:

SELECT
product_id
FROM
product_to_category
WHERE
category_id IN (
SELECT parent_id FROM category WHERE parent_id = '39'
)

Is what I'm trying to do here possible?

Answer 1

Got it:

SELECT product_id FROM product_to_category INNER JOIN category ON product_to_category.category_id = category.category_id AND category.parent_id = 39

Answer 2

select product_id from product_to_category as ptc
inner join category as c
    on ptc.category_id = c.parent_id
where c.parent_id = 39

Answer 3

Try this:

SELECT product_id
FROM product_to_category p2c
   Join category c 
      On c.category_id = p2c.category_id
Where parent_id = '39'

What you were doing wrong in your query was:

 SELECT product_id
 FROM product_to_category
 WHERE category_id IN 
     (SELECT category_id   -- THIS IS WHERE YOUR SQL WAS INCORRECT
      FROM category WHERE parent_id = '39')