How to find grandparents using SQL?

Question

I have table which has two columns

 ----------------------
|  NAME | FATHER_NAME  |
 ----------------------

DDL :

CREATE TABLE RELATION (NAME VARCHAR(20), FATHER_NAME VARCHAR(20));

INSERT INTO RELATION VALUES ('ADESH','ASHOK');
INSERT INTO RELATION VALUES ('ASHOK','KA');
INSERT INTO RELATION VALUES ('KA',NULL);
INSERT INTO RELATION VALUES ('ANSH','ADESH');
INSERT INTO RELATION VALUES ('BASH','ANSH');

INSERT INTO RELATION VALUES ('DEVEN','SUBASH');
INSERT INTO RELATION VALUES ('SUBASH','KA');

INSERT INTO RELATION VALUES ('PRAKASH',NULL);
INSERT INTO RELATION VALUES ('PRADEEP','PRAKASH');
INSERT INTO RELATION VALUES ('SAI','PRADEEP');

I have sample data here as shown in image.

As desired output would be

So I tried to solve this with CONNECT_BY or Recursive CTE

WITH REL_CTE AS
(
    SELECT NAME,FATHER_NAME, 1 AS L
    FROM RELATION 
    WHERE FATHER_NAME IS NULL

    UNION ALL

    SELECT R.NAME,R.FATHER_NAME,CTE.L+1
    FROM RELATION R JOIN REL_CTE CTE
    ON R.FATHER_NAME = CTE.NAME

)

SELECT * FROM REL_CTE

But could not get the desired result. Hence looking out for solution.

Have a look at [recursive cte](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017#d-using-a-recursive-common-table-expression-to-display-multiple-levels-of-recursion) — gomory-chvatal, Jun 25 '19 at 16:00
*"Can you help me with it?"* No @adesh, not with the (significant) lack of information we have. Sample data, expected results and your attempts are all needed here, you have enough reputation to know how to post a good question and what a bad one looks like. Take the time to make an effort and provide everything we need to help you. — Thom A, Jun 25 '19 at 16:03
@adesh, if you have trouble with the suggested recursive CTE, edit your question to include CREATE TABLE DDL and sample data INSERT statements, along with the query you've tried. — Dan Guzman, Jun 25 '19 at 16:05
@Larnu : I have added more details to my question plus the SQL which I tried. — adesh, Jun 25 '19 at 16:10
@gomory-chvatal I have tried using recursive CTE or Connect BY but could not desired sql. — adesh, Jun 25 '19 at 16:16
@DanGuzman I have included the details in question also SQL which I tried to get the desired output. — adesh, Jun 25 '19 at 16:37
@SeanLange I tried using recursive CTE but could not get exact output. — adesh, Jun 25 '19 at 16:38
The question is going through the reopen voting at the moment @adesh . This normally takes longer than the close, as less people have the privilege to do so. This is just one reason why writing a well formed question from the start is so important. — Thom A, Jun 25 '19 at 16:49

score 0 · Answer 1 · answered Jun 25 '19 at 22:19

One possible way to do this is with a recursive CTE as shown below. However if you always want exactly grandparents, i.e. me -> father -> grandfather, you can just do a self join which will be far more efficient than the CTE. Note, with the CTE you could get great-grandparents etc...

SELF JOIN

select distinct
    L2.FATHER_NAME as GRANDFATHER_NAME
from
    dbo.RELATION as L1
inner join
    dbo.RELATION as L2
    on
        L1.FATHER_NAME = L2.[NAME]
where
    L2.FATHER_NAME is not null

RECURSIVE CTE

WITH REL_CTE AS
(
-- the initial result will be every record from the relation
-- table with level 1
select NAME,FATHER_NAME, 1 as L
from RELATION

-- now start recursively walking up the tree
union all
select
      cte.NAME
    , rel.FATHER_NAME -- this will be the father at the coresponding level
                      -- so when L = 1 Father, L = 2 Grandfather, L = 3 Great Grandfather
    , cte.L+1 as L
from
    REL_CTE as cte
inner join
    dbo.RELATION as rel
    on
        cte.FATHER_NAME = rel.NAME

)
-- if you specifically want grandparents you would only
-- want things at level 2
select distinct REL_CTE.FATHER_NAME from REL_CTE
where L = 2 and REL_CTE.FATHER_NAME is not null

How to find grandparents using SQL?

1 Answers1