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

Transact-SQL
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