当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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

网友答案:

"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
分享给朋友:
您可能感兴趣的文章:
随机阅读: