I have a table which has self relationship:
id -- parentId columns
I need a query to get the parent row where the parentId is null, but I couldn't figure out the proper way of doing this.
select * from table1 where id = parentId;
Apparently this is not working, it will just give the direct parent.
"Get the parent row where the parentId is null" makes no sense, but in case you actually meant "get the parent row until the parentId is null", then this recursive CTE should do the trick:
WITH cte AS ( SELECT * FROM table1 WHERE id = 7 UNION ALL SELECT table1.* FROM table1 JOIN cte ON table1.id = cte.parentId ) SELECT * FROM cte
This returns the row with id = 7 and all its ancestors recursively. Replace 7 according to your needs.
SELECT * FROM table1 AS A LEFT JOIN table1 as B ON B.ID = A.parentID WHERE B.parentID IS NULL