CODEWITHSUNDEEP
sqlt-sql
Alaa Jabre
Alaa Jabre

Reputation: 1903

Table self related query

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.

Any help

Upvotes: 0

Views: 305

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

"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.

Upvotes: 1

Gixonita
Gixonita

Reputation: 805

SELECT *

FROM table1 AS A

LEFT JOIN table1 as B
  ON B.ID = A.parentID

WHERE B.parentID IS NULL

Upvotes: 0

Related Questions