I have a table which has hierarchy in it.
Lets start with
Id = 5; this is the child. (a given start parameter - from user)
firstvalue which you have encountered for
id = 5
id = 5, go to its parent and give me his
parentId = 0)
p.s. the result here should be 7.
if 7 value was empty so : 9
end if 9 was also empty so : 1
I'm trying to do this with CTE but no success..
p.s. I want a solution with CTE. :)
What I have tried (don't even look at it - it doesn't work):
WITH cte AS (
FROM [test].[dbo].[tmp] WHERE id=5
FROM [tmp] WHERE parentId=cte.parentId ) //ERROR : The multi-part identifier "cte.parentId" could not be bound.
SELECT * FROM cte
You need to join in your cte:
with cte as ( select id, value, parentid from [tmp] where id=5 union all select tmp.id, coalesce(cte.value, tmp.value) as value, tmp.parentid from [tmp] inner join cte on tmp.id = cte.parentId ) select max(value) as value from cte
If you're going to reference a table, you need to make sure that you've joined it somewhere in your
from clause. In this case, you hadn't, which is why it was throwing the error.