list out all parents, child grandchilds,etc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

list out all parents, child grandchilds,etc

Author

Topic

SQL_Rookie
Starting Member

32 Posts

Posted - 2012-11-08 : 10:46:26

I have read a few post on this site but most or all post use a parent to child id relationship (adjanceny model) my table is nested set model.
Here some sample data

create table Accounts
(
ID int,
TREE_ID int,
[Name] nvarchar(60),
LEFT_NUM int,
RIGHT_NUM int
)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (1, 250, 'Retailers',1,25)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (2, 250, 'A', 2,17)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (3, 250, 'AB', 3,4)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (4, 250, 'AC', 5,6)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (5, 250, 'AD', 7,8)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (6, 250, 'AE', 9,10)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (7, 250, 'AF', 11,12)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (8, 250, 'AG', 13,14)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (9, 250, 'AH', 15,16)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (10, 250, 'B', 20,21)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (11, 300, 'Whosalers',1,2)

Data looks like
Retailers
A
AB
AC
AD
AE
AF
AG
AH
B
Whosalers

I would like the result set to display like
Level 0 Level 1 Level 3
Retailers A AB
Retailers A AC
Retailers A AD
Retailers A AE
Retailers A AF
Retailers A AG
Retailers A AH
Retailers B null
Whosalers null null

I have tried to use a CTE statement but I get the
"The maximum recursion 100 has been exhausted before statement completion"
So I'm not sure if this is a good technique.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 11:55:34

What is the rule you are using to identify a parent/child relationship? Can you post the query you tried?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-08 : 12:03:58

ugh - I'm not crazy about the nested set model. Try this:


;with rcte ([id],accounts_id,[name],left_num,right_num,hierPath,lev)
as (
       select [ID]
              ,accounts_id
              ,[Name]
              ,left_num
              ,right_num
              ,convert(varchar(50), replace(str([id],3),' ','0')) 
              ,0
       from Accounts
       where left_num = 1

       union all

       select a.[ID]
              ,a.accounts_id
              ,a.[Name]
              ,a.left_num
              ,a.right_num
              ,convert(varchar(50), c.hierPath + '-' + replace(str(a.[id],3),' ','0'))
              ,c.lev + 1
       from   rcte c
       join   Accounts a
              on a.accounts_id = c.accounts_id
              and a.left_num > c.left_num 
              and a.left_num < c.right_num
       where  not exists (--a does not have any parents that are not already in rcte
              select *
              from   Accounts p
              where  p.accounts_id = a.accounts_id
              and    p.left_num < a.left_num
              and    p.right_num > a.right_num
              and    patindex('%' + replace(str(p.[id],3),' ','0') + '%', c.hierPath) = 0
              )
)

select accounts_id
       ,left(name, 15) name
       ,left_num
       ,right_num
       ,hierPath
       ,lev       
from rcte 
order by hierPath

OUTPUT:
accounts_id name            left_num    right_num   hierPath                                           lev
----------- --------------- ----------- ----------- -------------------------------------------------- -----------
250         Retailers       1           25          001                                                0
250         A               2           17          001-002                                            1
250         AB              3           4           001-002-003                                        2
250         AC              5           6           001-002-004                                        2
250         AD              7           8           001-002-005                                        2
250         AE              9           10          001-002-006                                        2
250         AF              11          12          001-002-007                                        2
250         AG              13          14          001-002-008                                        2
250         AH              15          16          001-002-009                                        2
250         B               20          21          001-010                                            1
300         Whosalers       1           2           011                                                0

Be One with the Optimizer
TG

SQL_Rookie
Starting Member

32 Posts

Posted - 2012-11-08 : 12:09:38

The Tree_ID links all the parents and child together then the left_num and right_num are used to position them as parents, child, child of child, etc...

select a.Name, b.Name, c.Name
from accounts a
left join accounts b on a.TREE_ID =b.TREE_ID
and b.LEFT_NUM between a.LEFT_NUM and a.RIGHT_NUM and a.Name != b.Name and a.LEFT_NUM =1
left join accounts c on b.TREE_ID =c.TREE_ID
and b.LEFT_NUM between b.LEFT_NUM and c.RIGHT_NUM and c.Name != b.Name and a.LEFT_NUM !=1

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-08 : 12:20:08

woops - my post is not right (yet)...hold on a sec.

EDIT:
Fixed the query in my original post

Be One with the Optimizer
TG

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-11 : 23:13:36

quote:
Originally posted by SQL_Rookie

I have read a few post on this site but most or all post use a parent to child id relationship (adjanceny model) my table is nested set model.
Here some sample data

create table Accounts
(
ID int,
TREE_ID int,
[Name] nvarchar(60),
LEFT_NUM int,
RIGHT_NUM int
)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (1, 250, 'Retailers',1,25)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (2, 250, 'A', 2,17)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (3, 250, 'AB', 3,4)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (4, 250, 'AC', 5,6)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (5, 250, 'AD', 7,8)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (6, 250, 'AE', 9,10)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (7, 250, 'AF', 11,12)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (8, 250, 'AG', 13,14)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (9, 250, 'AH', 15,16)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (10, 250, 'B', 20,21)
insert into Accounts (ID,Tree_ID,[Name],LEFT_NUM,RIGHT_NUM) Values (11, 300, 'Whosalers',1,2)

Data looks like
Retailers
A
AB
AC
AD
AE
AF
AG
AH
B
Whosalers

I would like the result set to display like
Level 0 Level 1 Level 3
Retailers A AB
Retailers A AC
Retailers A AD
Retailers A AE
Retailers A AF
Retailers A AG
Retailers A AH
Retailers B null
Whosalers null null

I have tried to use a CTE statement but I get the
"The maximum recursion 100 has been exhausted before statement completion"
So I'm not sure if this is a good technique.

This will give you exactly what you're looking for.

WITH
cteLevels AS
(
 SELECT a2.TREE_ID, a2.ID, a2.Name, a2.LEFT_NUM, a2.RIGHT_NUM, COUNT(a1.ID)-1 AS Level
   FROM Accounts AS a1 
   JOIN Accounts AS a2
     ON a2.LEFT_NUM BETWEEN a1.LEFT_NUM AND a1.RIGHT_NUM
    AND a1.Tree_ID = a2.Tree_ID
  GROUP BY a2.TREE_ID, a2.ID, a2.Name, a2.LEFT_NUM, a2.RIGHT_NUM
)
 SELECT Level0 = L0.Name, Level1 = L1.Name, Level2 = L2.Name
   FROM cteLevels L0
   LEFT OUTER JOIN cteLevels L1 ON L0.TREE_ID = L1.TREE_ID AND L1.LEFT_NUM BETWEEN L0.LEFT_NUM AND L0.RIGHT_NUM AND L1.Level = 1
   LEFT OUTER JOIN cteLevels L2 ON L1.TREE_ID = L2.TREE_ID AND L2.LEFT_NUM BETWEEN L1.LEFT_NUM AND L1.RIGHT_NUM AND L2.Level = 2
  WHERE L0.Level = 0
  ORDER BY L0.TREE_ID, L0.LEFT_NUM, L1.LEFT_NUM, L2.LEFT_NUM
;

Output:

Level0    Level1 Level2
--------- ------ ------
Retailers A      AB
Retailers A      AC
Retailers A      AD
Retailers A      AE
Retailers A      AF
Retailers A      AG
Retailers A      AH
Retailers B      NULL
Whosalers NULL   NULL

As a bit of a side bar, I'd recommmend using the SELECT in the CTE to update your nested sets with Level# to make your life easier in the future.

--Jeff Moden

SQL_Rookie
Starting Member

32 Posts

Posted - 2012-11-12 : 07:53:25

Is there away or method to use subqueries? As CTE is limited and this table holds lots of data. I'm expecting back 30K plus rows.

SQL_Rookie
Starting Member

32 Posts

Posted - 2012-11-12 : 08:47:38

[quote
As a bit of a side bar, I'd recommmend using the SELECT in the CTE to update your nested sets with Level# to make your life easier in the future.

--Jeff Moden
[/quote]

what do you mean about update the nested sets with level#?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-12 : 10:12:52

Will your 30K+ rows only ever have 3 levels? If so are you expecting an indefinite number of level columns created in the output?
My solution assumed and unknown number of levels and simply was to illustrate how to use the [left] and [right] values to express the hierarchy.

Be One with the Optimizer
TG

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-12 : 12:36:56

quote:
Originally posted by SQL_Rookie

Is there away or method to use subqueries? As CTE is limited and this table holds lots of data. I'm expecting back 30K plus rows.

I'm not sure where you get the idea that CTE's are limited. 30K rows is nothing to a CTE.

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-12 : 12:39:13

quote:
Originally posted by SQL_Rookie

[quote
As a bit of a side bar, I'd recommmend using the SELECT in the CTE to update your nested sets with Level# to make your life easier in the future.

--Jeff Moden

what do you mean about update the nested sets with level#?
[/quote]

Pretty much what I stated. Add a column to your nested sets to hold Level. Since the SELECT from the CTE very specifically finds the level for each row, you could use it to update the new Level column in you're nested sets so you don't have to recalculate the Level everytime you need it.

--Jeff Moden

SQL_Rookie
Starting Member

32 Posts

Posted - 2012-11-13 : 08:47:48

Is there away to get the parent id of each row ? I'm trying to have the end result to be a adjacency list basically. So if I took the query below
SELECT a2.TREE_ID, a2.ID, a2.Name, a2.LEFT_NUM, a2.RIGHT_NUM, COUNT(a1.ID)-1 AS Level
FROM Accounts AS a1
JOIN Accounts AS a2
ON a2.LEFT_NUM BETWEEN a1.LEFT_NUM AND a1.RIGHT_NUM
AND a1.Tree_ID = a2.Tree_ID
GROUP BY a2.TREE_ID, a2.ID, a2.Name, a2.LEFT_NUM, a2.RIGHT_NUM

it produces this results set
TREE_ID ID Name LEFT_NUM RIGHT_NUM Level
250 1 Retailers 1 25 0
250 2 A 2 17 1
250 3 AB 3 4 2
250 4 AC 5 6 2
250 5 AD 7 8 2
250 6 AE 9 10 2
250 7 AF 11 12 2
250 8 AG 13 14 2
250 9 AH 15 16 2
250 10 B 20 21 1
300 11 Whosalers 1 2 0

but I would like to get the ID from the parent.

TREE_ID ID Name LEFT_NUM RIGHT_NUM Level PARENT ID
250 1 Retailers 1 25 0 null
250 2 A 2 17 1 1
250 3 AB 3 4 2 2
250 4 AC 5 6 2 2
250 5 AD 7 8 2 2
250 6 AE 9 10 2 2
250 7 AF 11 12 2 2
250 8 AG 13 14 2 2
250 9 AH 15 16 2 2
250 10 B 20 21 1 1
300 11 Whosalers 1 2 0 null

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-13 : 10:30:16

I'll let Jeff update his version. Here's mine:


with rcte ([id],parentid,tree_id,[name],left_num,right_num,hierPath,lev)
as (
       select [ID]
              ,null
              ,tree_id
              ,[Name]
              ,left_num
              ,right_num
              ,convert(varchar(50), replace(str([id],3),' ','0')) 
              ,0
       from Accounts
       where left_num = 1

       union all

       select a.[ID]
              ,c.[id]
              ,a.tree_id
              ,a.[Name]
              ,a.left_num
              ,a.right_num
              ,convert(varchar(50), c.hierPath + '-' + replace(str(a.[id],3),' ','0'))
              ,c.lev + 1
       from   rcte c
       join   Accounts a
              on a.tree_id = c.tree_id
              and a.left_num > c.left_num 
              and a.left_num < c.right_num
       where  not exists (--a does not have any parents that are not already in rcte
              select *
              from   Accounts p
              where  p.tree_id = a.tree_id
              and    p.left_num < a.left_num
              and    p.right_num > a.right_num
              and    patindex('%' + replace(str(p.[id],3),' ','0') + '%', c.hierPath) = 0
              )
)

select tree_id
       ,id
       ,left(name, 15) name
       ,left_num
       ,right_num
       --,hierPath
       ,lev       
       ,parentid
from rcte 
order by hierPath

OUTPUT:
tree_id     id          name            left_num    right_num   lev         parentid
----------- ----------- --------------- ----------- ----------- ----------- -----------
250         1           Retailers       1           25          0           NULL
250         2           A               2           17          1           1
250         3           AB              3           4           2           2
250         4           AC              5           6           2           2
250         5           AD              7           8           2           2
250         6           AE              9           10          2           2
250         7           AF              11          12          2           2
250         8           AG              13          14          2           2
250         9           AH              15          16          2           2
250         10          B               20          21          1           1
300         11          Whosalers       1           2           0           NULL

Be One with the Optimizer
TG

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-13 : 11:12:36

Post removed. There was a bug in the code

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-13 : 11:32:12

>> Might as well use the power built into the Nested Sets to do this
agreed.
I would be curious to see stat comparisons for real data.

recursive cte:
Table 'Accounts'. Scan count 3, logical reads 28
Table 'Worktable'. Scan count 2, logical reads 65

intended query for nested sets:
Table 'Accounts'. Scan count 12, logical reads 12

Be One with the Optimizer
TG

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-14 : 04:04:54

quote:
Originally posted by TG

>> Might as well use the power built into the Nested Sets to do this
agreed.
I would be curious to see stat comparisons for real data.

recursive cte:
Table 'Accounts'. Scan count 3, logical reads 28
Table 'Worktable'. Scan count 2, logical reads 65

intended query for nested sets:
Table 'Accounts'. Scan count 12, logical reads 12

Be One with the Optimizer
TG

Especially if there were an index on the left and right bowers.

I will admit, though, that Nestd Sets aren't known for the "upline" capabilities. I suspect that a TOP 1 with a descending ORDER BY instead of using the "MAX" would be more gentle on the scan count especially in the presence of an index.

If you'd like to test, I just published an article on how to build a million node hierarchy at the following URL. It has an Adjacency List generator that will build a million row Adjacency List in about 7 seconds and a converter to convert it to Nested Sets in about 54.
[url]http://www.sqlservercentral.com/articles/Hierarchy/94040/[/url]

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources