Want to find out all possible parent and child rows against a specific id?
How do I find out all possible parent and child rows against a specific id?
eg. have the following table:
MyTable:
-----------------------------------------------------
| Id | PId | Description |
-----------------------------------------------------
| 1 | NULL | A is Parent |
| 2 | 1 | B is Child of A |
| 3 | 2 | C is Child of B |
| 4 | NULL | D is Parent |
| 5 | NULL | E is Parent |
| 6 | 5 | F is Child of E |
-----------------------------------------------------
want to know all possible parent and child elements when passing spesific id
eg.
CASE-01:
When @MyLookupId = 2 OR @MyLookupId = 1 OR @MyLookupId = 3 One of them Then the result should be,
-------
| Id |
-------
| 1 |
| 2 |
| 3 |
-------
CASE-02:
When @MyLookupId = 4 then the result should be,
-------
| Id |
-------
| 4 |
-------
CASE-03:
When @MyLookupId = 6 then the result should be,
-------
| Id |
-------
| 5 |
| 6 |
-------
Here is the SQL for the table:
IF OBJECT_ID('tempdb.dbo.#MyTable', 'U') IS NOT NULL DROP TABLE #MyTable;
SELECT * INTO #MyTable FROM (
SELECT (1)Id, (NULL)PId, ('A IS Parent')Description UNION ALL
SELECT (2)Id, (1)PId, ('B IS Child of A')Description UNION ALL
SELECT (3)Id, (2)PId, ('C IS Child of B')Description UNION ALL
SELECT (4)Id, (NULL)PId, ('D IS Parent')Description UNION ALL
SELECT (5)Id, (NULL)PId, ('E IS Parent')Description UNION ALL
SELECT (6)Id, (5)PId, ('F IS Child of E')Description ) AS tmp
SELECT * FROM #MyTable
source to share
The answer given by TriV works, but requires the entire hierarchy of your source table to be computed every time a query is run, which may not work well on a larger scale.
A narrower approach is to find parent and child records that only relate to the ID
one you are looking for:
declare @t table(ID int, PID int);
insert into @t values(1,null),(2,1),(3,2),(4,null),(5,null),(6,5);
declare @ID int = 2;
with c as
(
select ID
,PID
from @t
where ID = @ID
union all
select t.ID
,t.PID
from @t t
join c
on(t.PID = c.ID)
)
,p as
(
select ID
,PID
from @t
where ID = @ID
union all
select t.ID
,t.PID
from @t t
join p
on(t.ID = p.PID)
)
select ID
from p
union all
select ID
from c
where c.ID <> @ID
order by ID;
Output:
ID
````
1
2
3
source to share
you can use recursive cte
-- temp returns full tree of each rootId (parentid = null)
;WITH temp AS
(
SELECT sd.Id, sd.PId, sd.Id AS RootId
FROM #MyTable sd
WHERE sd.PId IS NULL
UNION ALL
SELECT sd.Id, sd.PId, t.RootId
FROM temp t
INNER JOIN #MyTable sd ON t.Id = sd.PId
)
SELECT t2.Id
FROM temp t
INNER JOIN temp t2 ON t2.RootId = t.RootId
WHERE t.Id = @Id
OPTION (MAXRECURSION 0)
Demo link: http://rextester.com/RAITMT72805
source to share