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.

You need to sign in to view this answers