Last post Jul 29, 2016 06:58 AM by eralper
Jul 28, 2016 04:27 PM|Outlooker|LINK
Hello my friends,
I've two tables for my employees data, one for their name and ID and another for their relationship.
The relationship is as follows:
It is about how an employee has relationship to another.
Now I want to find the first ID in which each employee has relationship with his/her.
For instance, in the example parent for ID=14 is 11 but actually, the real parent is 10 because of cycles and hierarchy.
May you please tell me how can I do that?
Jul 29, 2016 05:01 AM|balu.devara|LINK
can you try this code.
select id from tempTable where id in(
select a.parentid from tempTable a where a.id=14) and parentid is null
Jul 29, 2016 06:58 AM|eralper|LINK
What you expect to solve can be managed using
Recursive CTE queries in SQL Server
Once you get the main syntax and idea behind, you can easily implement it on other examples
Please try following SQL CTE query
declare @id int = 14
;with cte as (
select id, parent from relationship where id = @id
select r.id, r.parent
from relationship r
inner join cte on r.id = cte.parent
select @id child, id parent
where parent is null
I hope it helps