What is the query to get parent records against specific child or get child records against parent?

Question

Scenario:

I have data in the following hierarchy format in my table:

PERSON_ID   Name    PARENT_ID
1           Azeem   1
2           Farooq  2
3           Ahsan   3
4           Waqas   1
5           Adnan   1
6           Talha   2
7           Sami    2
8           Arshad  2
9           Hassan  8

E.g

Hassan is child of parent_id 8 which is (Arshad)

and Arshad is child of parent_id 2 which is (Farooq)

What I want:

First of all, I want to find all parent of parent of specific parent_id.

For Example: If I want to find the parent of Hassan then I also get the Parent of Hassan and also get its parent (Hassan -> Arshad -> Farooq)

Second, I want to find all child of Farooq like (Farooq -> Arshad -> Hassan)

Third, If Azeem is also have same parent like (Azeem -> Azeem) then show me this record.

What I've tried yet:

DECLARE @id INT
SET @id = 9

;WITH T AS (
    SELECT p.PERSON_ID,p.Name, p.PARENT_ID
        FROM hierarchy p
        WHERE p.PERSON_ID = @id AND p.PERSON_ID != p.PARENT_ID
    UNION ALL
    SELECT c.PERSON_ID,c.Name, c.PARENT_ID
        FROM hierarchy c
        JOIN T h ON h.PARENT_ID = c.PERSON_ID)
 SELECT h.PERSON_ID,h.Name FROM T h

and Its shows me below error:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Answer 1

If I understand your question correctly that you don't want to insert null values in Parent_ID column then you should replace NULL with 0 and your updated code will be like:

;WITH DATA AS (
                SELECT p.PERSON_ID,p.Name, p.PARENT_ID
                FROM hierarchy p
                WHERE p.PERSON_ID = 9
                UNION ALL
                SELECT c.PERSON_ID,c.Name, c.PARENT_ID
                FROM hierarchy c
                JOIN DATA h 
                ON c.PERSON_ID = h.PARENT_ID 
          )
select * from DATA;

Answer 2

Well I found a way for my above case which is:

If I have below table structure:

PERSON_ID   Name    PARENT_ID
1           Azeem   NULL
2           Farooq  NULL
3           Ahsan   NULL
4           Waqas   1
5           Adnan   1
6           Talha   2
7           Sami    2
8           Arshad  2
9           Hassan  8

Then I tried below query which working fine in case when Parent_ID have NULL values means there is no more parent of that record.

DECLARE @id INT
SET @id = 2

Declare @Table table(
    PERSON_ID bigint,
    Name varchar(50),
    PARENT_ID bigint
    );

;WITH T AS (
SELECT p.PERSON_ID,p.Name, p.PARENT_ID
    FROM hierarchy p
    WHERE p.PERSON_ID = @id AND p.PERSON_ID != p.PARENT_ID
UNION ALL
SELECT c.PERSON_ID,c.Name, c.PARENT_ID
    FROM hierarchy c
    JOIN T h ON h.PARENT_ID = c.PERSON_ID)
insert into @table 
select * from T;

IF exists(select * from @table)
BEGIN
    select PERSON_ID,Name from @table
End
Else
Begin
    select PERSON_ID,Name from Hierarchy
    where PERSON_ID = @id
end

Above query show me the desire output when I set the parameter value @id = 1

Above query show me the desire output when I set the parameter value @id = 9

Issue:

I don't want to insert null values in Parent_ID like if there is no more Parent of that Person then I insert same Person_ID in Parent_ID column. If I replace null values with there person_id then I got below error.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Answer 3

You have an infinite loop in your data: Azeem is his own parent. You need to either make his value NULL or change your condition to WHERE p.parent_id = @id AND p.parent_id != p.child_id.

Also, I feel you have your columns named the wrong way around - the primary-key should be named person_id instead of parent_id and your column named child_id actually points to that person's parent, so it should be named parent_id instead.