如何从两个不同的表中获取数据层次结构？

I have two tables TwitterStatus and twitterstatusDetails,

我有两个表TwitterStatus和twitterstatusDetails，

I have data respectively in following manner.

我有以下方式分别有数据。

Here as given in twitterstatus table 4th record with the ts_StatusID "587573444633427968" is parent of twitterstatus_Details table 3rd record, which having td_inReplyTostatusID value is "587573444633427968", for next record td_StatusID of recent child record is parent of twitterstatus table having that child ts_StatusID value in ts_InreplytoStatusID field, so like that it is the hierarchy, so

这里在twitterstatus表中给出第4条记录，其中ts_StatusID“587573444633427968”是twitterstatus_Details表第3条记录的父级，其td_inReplyTostatusID值为“587573444633427968”，对于下一条记录，最近子记录的td_StatusID是具有该子级ts_StatusID值的twitterstatus表的父级ts_InreplytoStatusID字段，所以就像它是层次结构一样

how can I get the records from both the tables based on child parent relationship?

still any concern then please ask in comment.

仍有任何疑虑，请在评论中提问。

Thank you.

谢谢。

1 个解决方案

#1

I think what you are looking for is recursive CTE to get every nested level. Hope this get you closer to your goal.

我认为您正在寻找的是递归CTE以获得每个嵌套级别。希望这能让你更接近你的目标。

--REPLACE @TwitterStatus and @twitterstatus_Details with your tablename.
;WITH CteTStatus
AS
(
    SELECT h.*, d.*, 0 as lvl
    FROM  @TwitterStatus AS h
    INNER JOIN @twitterstatus_Details AS d ON h.id_StatusID = d.td_InreplytoStatusID
    WHERE ts_StatusID = '587573444633427968'--WHERE CLAUSE TO GET ROOT PARENT
    UNION ALL -- UNION TO DIG INTO CHILD's
    SELECT h.*, d.*, lvl + 1 as lvl
    FROM  @TwitterStatus AS h
    INNER JOIN @twitterstatus_Details AS d ON h.id_StatusID = d.td_InreplytoStatusID
    INNER JOIN CteTStatus AS Parent ON h.ts_InreplytoStatusID = Parent.td_StatusID
)

SELECT *
FROM CteTStatus