I'm a noob in the recursive domain in that I understand the syntax but I'm unable to apply it to my current problem. I'm using Postgre SQL.
I have a product_id 'ABC123' that has a parent category 'FG_ACC_CA', which itself has parent category 'FG_ACC', which also has a parent category 'FG'. The 'FG' does not have a parent category.
I'm trying to use recursive query to find the top most parent of the given product_id. Here parent_id & product_id are columns and product_category & product_product are their respective tables.
The syntax that I'm trying to use is this enter image description here
Here's the query I've written:
with recursive ancestor as
(
select pc.parent_id as temp
from product_product pp
left join product_category pc on pc.id = pp.categ_id
where pp.product_id = 'ABC123'
union
select temp
from ancestor a
where a.temp != Null
)
select *
from ancestor
;
The results I get is just the parent_id of ABC123. So I guess I'm butchering the recursive part after union.
Also while researching I've come across multiple parent-child examples but they all use 'inner join' instead of 'where' even though they explain the concept with 'where' as a recursive condition. Couldn't figure out why.
In the recursive clause, the recursive table (product_category) must be included in the query and joined with the results of the recursion (ancestor).
with recursive ancestor as (
select pc.parent_id as temp
from product_product pp
left join product_category pc on pc.id = pp.categ_id
where pp.product_id = 'ABC123'
union all
select pc.parent_id as temp
from product_category pc
inner join ancestor a on a.temp = pc.id
where a.temp is not null
)
select temp from ancestor;