I have a table foo
with the content
So the records of the table foo can graphically be represented with this graph.
When I run a procedure that should return ancestors using @starting_id=9
(represented with a circle in the graph), I get this result
- 9
instead of
- 9
- 3 and
- 1
Why?
CREATE PROCEDURE [dbo].[GetParents]
@starting_id int
AS
BEGIN
WITH chainIDsUpwards AS
(
SELECT id, parent_id FROM foo WHERE id = @starting_id
UNION ALL
SELECT foo.id, foo.parent_id FROM foo
JOIN chainIDsUpwards p ON p.id = foo.parent_id
)
SELECT id FROM chainIDsUpwards
END
Fiddle at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=81be3d86dc7581eb60bc7af4c09077e4
Best Answer
I believe you made a mistake in your join predicate. Try:
It is a common mistake, and I often do it myself. I find it easiest to get it right if I start with the CTE:
and then think of the CTE as a linked list where I need to find the next element: