如何在使用父ID进行搜索时查询产品？

Question

refer to

How to count product in this category?

TABLE CATEGORY

 category_id  
 title 
 lft 
 rght 
 parent 
 level

TABLE PRODUCT

 product_id 
 category_id 
 title

DATA Table category

|1|Electronics|1|16|0|0
|2|Televisions|2|7|1|1
|3|LCD|3|4|2|2
|4|PLASMA|5|6|2|2
|5|Players|8|15|1|1
|6|Mp3 players|9|10|5|2
|7|CD players|11|12|5|2
|8|DVD players|13|14|5|2
|9|Furniture|17|18|0|0

if i want to query product to show all i will query in table product

SELECT * from product order by product_id

but if i want to query by parent id How will i query?

i tried to query

select product.*
from category as node, category as parent, product  
where node.lft between parent.lft and parent.rght   
and node.category_id = product.category_id  
group by parent.title   
order by node.lft

but Does not meet the requirements.

if i will to search Televisions category_id = 2 I want to show product in category_id 2 same example

Example
|1|4|Plasma 1
|2|4|Plasma 2
|3|4|Plasma 3
|4|4|Plasma 4
|5|4|Plasma 5
|6|3|LCD 1
|7|3|LCD 2
|8|3|LCD 3

Please help me. Thank you for the answers.

http://i.stack.imgur.com/2kxzt.png img DATA in Table category

Answer 1

Have no possibility to debug that, and have no idea what is your lft and rgth for.

But to get result you need, you can try:

SELECT product.*
from product
LEFT JOIN category as cat 
ON product.category_id = cat.category_id 
WHERE cat.parent = 2