sqlpostgresqlrecursionrecursive-query

How to use recursive SQL to find the parent ID


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.


Solution

  • 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;