CODEWITHSUNDEEP
sqlsql-server
Sinan AKYAZICI
Sinan AKYAZICI

Reputation: 3952

How to get second parent with recursive query in Common Table

I am using SQL Server 2008. I have a table like this:

UnitId ParentId  UnitName
---------------------------
1        0       FirstUnit
2        1       SecondUnit One  
3        1       SecondUnit Two
4        3           B
5        2           C
6        4           D
7        6           E
8        5           F

I want to get second parent of the record. For example:

If I choose unit id that equal to 8, It will bring unit id is equal to 2 to me. It needs to be SecondUnit One. or If I choose unit id that equal to 7, It will bring unit id is equal to 3 to me. It needs to be SecondUnit Two.

How can I write a SQL query this way?

Upvotes: 4

Views: 1108

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

It took me a while, but here it is :)

with tmp as (
  select unitId, parentId, unitName, 0 as iteration
  from t
  where unitId = 7
  union all
  select parent.unitId, parent.parentId, parent.unitName, child.iteration + 1
  from tmp child
  join t parent on child.parentId = parent.unitId
  where parent.parentId != 0
)
select top 1 unitId, parentId, unitName from tmp
order by iteration desc

Here is also a fiddle to play with.

Upvotes: 6

Miika L.
Miika L.

Reputation: 3353

SELECT t.*, tParent1.UnitId [FirstParent], tParent2.UnitId [SecondParent]
FROM Table t
    LEFT JOIN Table tParent1 ON t.ParentId = tParent1.UnitId
    LEFT JOIN Table tParent2 ON tParent1.ParentId = tParent2.UnitId
WHERE t.UnitId = <Unit ID search here>
    AND NOT tParent2.UnitId IS NULL

Edit: And leave out second part of the WHERE clause if you want results returned even if they don't have a second parent.

Upvotes: 1

Related Questions