How to know all possible Parent and Child Rows against specific Id?
e.g. have 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 when pass spesific id
e.g.
CASE-01:
When @MyLookupId=2 OR @MyLookupId=1 OR @MyLookupId=3 One of from them Then Result Should Be,
-------
| Id |
-------
| 1 |
| 2 |
| 3 |
-------
CASE-02:
When @MyLookupId=4 Then Result Should Be,
-------
| Id |
-------
| 4 |
-------
CASE-03:
When @MyLookupId=6 Then Result Should Be,
-------
| Id |
-------
| 5 |
| 6 |
-------
Here is SQL for 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