SQL - How to get Treeview of GrandParent, Parent, Child by ID

Question

I have 2 tables, where Customer table has customer data and relations table has relations of customer.

CREATE Table Customer  
(  
  id int,  
  name nvarchar(10)  
)

INSERT INTO Customer Values  
(1, 'aaa'),  
(2, 'bbb'),  
(3, 'ccc'),  
(4, 'ddd'),  
(5, 'eee'),  
(6, 'fff'),  
(7, 'ggg'),  
(8, 'hhh'),  
(9, 'iii'),  
(10, 'jjj'),  
(11, 'kkk'),  
(12, 'lll')  

CREATE TABLE Relations  
(  
  id int,  
  parentid int  
)  

INSERT INTO Relations VALUES  
  (2,  1),  
  (3,  1),  
  (4,  2),  
  (5,  2),  
  (6,  1),  
  (7,  4),  
  (8,  5),  
  (9,  8),  
  (10, 8),  
  (12, 7)

I want to find GrandParent, Parent and child by ID. For Ex: If I want to find all relations of ID=4, I should get the result in the below format. Ordered by Grand Parent at the top if the ID has a parent or Grand parent. If not, then it has to show the child of that ID.

Grand Parent | aaa
Parent | bbb
Child | ggg
Child | lll

Could you please help me with the above query in "SQL Server".

You'll need to do a [recursive cte](https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example) to climb and descend your hierarchy. — JNevill, Sep 23 '19 at 19:50
Try to specify a little better what you do mean, For the example given, "4" would yield something that may be outside of your specification: Grand Parent(1) > Parent(2) > Your Node(4) > Child of Your Node(7). — Gabriel Fonseca, Sep 23 '19 at 19:50
Possible duplicate of [SQL Server CTE and recursion example](https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example) — Zack, Sep 23 '19 at 19:51

score 0 · Accepted Answer · answered Sep 23 '19 at 19:57

You can use recursives CTEs (Common Table Expressions) to get this information. For example, for ID = 4 you can do:

with
a as ( -- get ancestors
  select 0 as lvl, id, name from customer where id = 4
  union all 
  select a.lvl - 1, c.id, c.name
  from a
  join relations r on a.id = r.id
  join customer c on c.id = r.parentid
),
d as ( -- get descendants
  select 0 as lvl, id, name from customer where id = 4
  union all 
  select d.lvl + 1, c.id, c.name
  from d
  join relations r on d.id = r.parentid
  join customer c on c.id = r.id
)
select lvl, id, name from a
union 
select lvl, id, name from d
order by lvl

Result:

lvl  id  name
---  --  ----
 -2   1  aaa 
 -1   2  bbb 
  0   4  ddd 
  1   7  ggg 
  2  12  lll

SQL - How to get Treeview of GrandParent, Parent, Child by ID

1 Answers1