# Unexpected results when using recursive CTE to find grand parent of a child

#1

Hi,
I have a table with "children" and "parents" and would like the output with the most senior "grandparent" ("parent" if no "grandparent") for each "child".

``````CREATE TABLE #MyTable (c char, p char)
INSERT INTO #MyTable VALUES ('a', 'b'), ('b', 'c'), ('c', 'd'),
('m', 'n'), ('n', 'p'), ('x', 'y')
``````

Column "c" stands for a "child", column "p" for a "parent".

I am using the followitg recursive CTE to get the needed output:

``````;WITH q(c, p, i)
AS (
SELECT m1.c, COALESCE(m1.p, m2.p), CASE WHEN m2.c is null THEN 1 ELSE 0 END
FROM #MyTable m1
LEFT JOIN #MyTable m2 ON m1.p = m2.c

UNION ALL
SELECT q.c, COALESCE(m.p, q.p), CASE WHEN m.c is null THEN 1 ELSE 0 END
FROM q
INNER JOIN #MyTable m ON q.p = m.c
WHERE q.i = 0
)
SELECT * FROM q
``````

where column "i" stands for the status of the most senior "grandparent" found: 1 true, 0 false; and I get the following output:

``````   c p i
1  a b 0
2  b c 0
3  c d 1
4  m n 0
5  n p 1
6  x y 1
7  m p 0
8  b d 0
9  a c 0
10 a d 0
``````

I do get results that include the pairs of "children" and most senior "grandparents" that I am looking for, but I don't understand why, for example, I get 0 value in the most senior "grandparent" found column for ('a', 'd') "children"/most senior "grandparent" pair where it ought to be value 1 like so:

``````   c p i
1  a b 0
2  b c 0
3  c d 1
4  m n 0
5  n p 1
6  x y 1
7  m p 1
8  b d 1
9  a c 0
10 a d 1
``````

I would appreciate any suggestions about why I'm not getting the desired output or the approach how to do it,
Viktors

#2

Try this:

``````WITH q(c,p)
AS (SELECT c
,p
FROM #MyTable AS m1
UNION ALL
SELECT q.c
,m.p
FROM #MyTable AS m
INNER JOIN q
ON q.p=m.c
)
SELECT q.c
,q.p
,CASE WHEN m.c IS NULL THEN 1 ELSE 0 END AS i
FROM q AS q
LEFT OUTER JOIN #MyTable AS m
ON m.c=q.p
;
``````

#3

bitsmed,

The sql that you shared gives the results that I need. And I think I understand how it works that is great.

Thanks a lot!
Viktors