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.

I removed the [tag:mysql] tag because I doubt you're using MySQL. It looks like you're using Microsoft SQL Server, so I added that tag. — Bill Karwin, Sep 06 '17 at 22:40
You have an infinite loop in your data: `Azeem` is his own parent. You need to either make the value `NULL` or change your condition to `WHERE p.parent_id = @id AND p.parent_id != p.child_id`. — Dai, Sep 06 '17 at 22:43

score 1 · Answer 1 · answered Sep 06 '17 at 22:44

1

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.

answered Sep 06 '17 at 22:44

Dai

141,631
28
261
374

as per your format I've edit my post also I add one more condition which is if Azeem its own parent then show me this record also. If I put null value in Parent_ID column It returns data but when in parameter `@id` I pass value of 2 and its Parent_ID is null then It doesn't give any record. But I want if Person_ID and Parent_ID is same then It show me the record. – Ahmer Ali Ahsan Sep 07 '17 at 06:50
Why my post down voted is something I missing in my question? – Ahmer Ali Ahsan Sep 07 '17 at 07:25
Kindly see my answer. – Ahmer Ali Ahsan Sep 07 '17 at 22:05

score 1 · Accepted Answer · answered Sep 09 '17 at 14:39

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;

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

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.

Specify the maxrecursion option at the end of the query: ... from EmployeeTree option (maxrecursion 0) That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion. — Brainsbot, Sep 07 '17 at 09:28
https://stackoverflow.com/questions/9650045/the-maximum-recursion-100-has-been-exhausted-before-statement-completion — Brainsbot, Sep 07 '17 at 09:29
@Brainsbot I saw this post and apply the accepted answer in my code but this didn't work in my scenario. Loop doesn't stop it continuously running unless I press the stop execution button to break the recursion loop. — Ahmer Ali Ahsan, Sep 07 '17 at 09:51

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

3 Answers3