How to use the connection in my case Postgres request?

Question

I am trying to get the product nameand categ_id. I have two tables:

product_template

categ_id     name
7            Nokia classic
7            Nokia lumia
8            samsung s3
6            huawai

There is one in this table productthat I want to get,

product_category

id       name         parent_id   
6        phones       3
7        nokia        6
8        samsung      6

This table shows that the product is under phone > nokiaand phone > samsung, or the product can be directly under the phone, as shown below,

as

phones > huawai
phones > nokia   > Nokia classic
phones > nokia   > Nokia lumia
phones > samsung > samsung s3

The request I use is

select pt.categ_id,pt.name from product_template pt inner join product_category pc on
pt.categ_id=pc.id where pc.parent_id='6'

it shows all products except huawai

must be implemented so that it can directly access products under phone phone > nokia > Nokia Classic

Thanks in advance for your suggestions.

Answer 1

, :

select pt.categ_id, pt.name
from product_template pt
inner join product_category pc on pt.categ_id = pc.id
where '6' in (pc.id, pc.parent_id)

1 . , :

with recursive rpc(id) AS (
    select '3'::int
  union all
    select id
    from product_category pc
    where pc.parent_id = rpc.id
)
select pt.categ_id, pt.name
from product_template pt
inner join rpc on pt.categ_id = rpc.id