| Author |
 Topic  |
|
|
SQL_Rookie
Starting Member
32 Posts |
 Posted - 11/08/2012 : 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. |
Edited by - SQL_Rookie on 11/08/2012 10:49:32
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/08/2012 : 11:55:34
|
| What is the rule you are using to identify a parent/child relationship? Can you post the query you tried? |
 |
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/08/2012 : 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 |
Edited by - TG on 11/08/2012 15:32:51 |
|
|
|
SQL_Rookie
Starting Member
32 Posts |
Posted - 11/08/2012 : 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
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/08/2012 : 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 |
Edited by - TG on 11/08/2012 15:33:34 |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 11/11/2012 : 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 |
Edited by - Jeff Moden on 11/11/2012 23:27:19 |
|
|
|
SQL_Rookie
Starting Member
32 Posts |
Posted - 11/12/2012 : 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 - 11/12/2012 : 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
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/12/2012 : 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
USA
643 Posts |
Posted - 11/12/2012 : 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
USA
643 Posts |
Posted - 11/12/2012 : 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 - 11/13/2012 : 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 |
Edited by - SQL_Rookie on 11/13/2012 08:48:52 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/13/2012 : 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
USA
643 Posts |
Posted - 11/13/2012 : 11:12:36
|
| Post removed. There was a bug in the code |
Edited by - Jeff Moden on 11/16/2012 22:53:01 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/13/2012 : 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
USA
643 Posts |
Posted - 11/14/2012 : 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.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
--Jeff Moden |
Edited by - Jeff Moden on 11/14/2012 04:11:42 |
|
|
| |
Topic |
|
|
|
list out all parents, child grandchilds,etc
