开发者

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

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 wha开发者_StackOverflowt 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?


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


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')


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜