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