1

I am using SQL Server 2012, I have a table called [Area]. This table contains a PK and a ParentPK. The ParentPk references the PK from the same [Area] table recursively. If there is no parent, then null is filled in for ParentPk.

Pk     ParentPk
----------------
1      null
2      null
3      1
4      3
...

I know that the ancestor-child relationship is exactly 3 levels deep. In the above example, The Pk:4 has its parentPk:3 and grandParentPk:1.

I want to be able have a SELECT query be in the form:

SELECT GrandParentPk, ParentPk, ChildPk
...
...
...
where ChildPk = <childPk>

Is there a non stored procedure, non recursive solution to achieve this?

user2769810
  • 155
  • 3
  • 12
  • Possible duplicate of [SQL Server Equivalent of Oracle 'CONNECT BY PRIOR', and 'ORDER SIBLINGS BY'](http://stackoverflow.com/questions/31827817/sql-server-equivalent-of-oracle-connect-by-prior-and-order-siblings-by) – PM 77-1 Mar 29 '17 at 19:56
  • I will take a look. Thanks! – user2769810 Mar 29 '17 at 21:00

1 Answers1

1

If you are always querying using the last child pk, and the hierarchy is always three levels, you can just use two inner joins.

select gp.pk as GrandParentPk, p.pk as ParentPk, c.pk as ChildPk
from Area c
  inner join Area p
    on c.parentPk = p.pk
  inner join Area gp 
    on p.parentPk = gp.pk
where c.pk = 4

rextester demo: http://rextester.com/MSXTVR55260

returns:

+---------------+----------+---------+
| GrandParentPk | ParentPk | ChildPk |
+---------------+----------+---------+
|             1 |        3 |       4 |
+---------------+----------+---------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59