SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 list out all parents, child grandchilds,etc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQL_Rookie
Starting Member

32 Posts

Posted - 11/08/2012 :  10:46:26  Show Profile  Reply with Quote
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.

Edited by - SQL_Rookie on 11/08/2012 10:49:32

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/08/2012 :  11:55:34  Show Profile  Reply with Quote
What is the rule you are using to identify a parent/child relationship? Can you post the query you tried?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 11/08/2012 :  12:03:58  Show Profile  Reply with Quote
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

Edited by - TG on 11/08/2012 15:32:51
Go to Top of Page

SQL_Rookie
Starting Member

32 Posts

Posted - 11/08/2012 :  12:09:38  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 11/08/2012 :  12:20:08  Show Profile  Reply with Quote
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

Edited by - TG on 11/08/2012 15:33:34
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/11/2012 :  23:13:36  Show Profile  Reply with Quote
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

Edited by - Jeff Moden on 11/11/2012 23:27:19
Go to Top of Page

SQL_Rookie
Starting Member

32 Posts

Posted - 11/12/2012 :  07:53:25  Show Profile  Reply with Quote
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.
Go to Top of Page

SQL_Rookie
Starting Member

32 Posts

Posted - 11/12/2012 :  08:47:38  Show Profile  Reply with Quote
[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#?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 11/12/2012 :  10:12:52  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/12/2012 :  12:36:56  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/12/2012 :  12:39:13  Show Profile  Reply with Quote
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
Go to Top of Page

SQL_Rookie
Starting Member

32 Posts

Posted - 11/13/2012 :  08:47:48  Show Profile  Reply with Quote
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

Edited by - SQL_Rookie on 11/13/2012 08:48:52
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 11/13/2012 :  10:30:16  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/13/2012 :  11:12:36  Show Profile  Reply with Quote
Post removed. There was a bug in the code

Edited by - Jeff Moden on 11/16/2012 22:53:01
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 11/13/2012 :  11:32:12  Show Profile  Reply with Quote
>> 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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/14/2012 :  04:04:54  Show Profile  Reply with Quote
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.
http://www.sqlservercentral.com/articles/Hierarchy/94040/


--Jeff Moden

Edited by - Jeff Moden on 11/14/2012 04:11:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000