How to use join in my case Postgres query ?

Question

i am trying to get product's name and categ_id. I have two tables that are,

product_template

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

this table have product that i want to get,

product_category

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

this table shows that which product is under phone > nokia and phone > samsung or product can be directly under phone as shown under,

like,

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

query i am using 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 is showing all products except huawai ???

query should run like that it can get products that are under phone directly and under this way phone > nokia > Nokia Classic

Thanks in advance for your suggestions.

pozs · Accepted Answer · 2014-06-06 10:06:35Z

4

If you want to fetch only the templates under a category, and the templates under that category's children categories, use that query:

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)

But that selects only templates within 1 level range of the selected category. If you want to select all descendant categories' templates, you can use the recursive common table expression:

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

answered Jun 6, 2014 at 10:06

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use join in my case Postgres query ?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related