Binary Tree Node Counting Problem Please Help...

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 2008 Forums

Transact-SQL (2008)

Binary Tree Node Counting Problem Please Help...

Author

Topic

talktosivas
Starting Member

6 Posts

Posted - 2011-08-02 : 10:00:08

Hi all...

i am new to SQL Server 2008.

For Creating Table :


CREATE TABLE binary_tree (
  aid bigint UNIQUE,
  name varchar(150) NOT NULL,
  parent_aid bigint NULL,
  position char(1) NULL,
  current_level int NULL,
  doj date NOT NULL,
  status INT NOT NULL DEFAULT '1')

Inserting Values :

This is Root node so parent_aid and position should be NULL


INSERT INTO binary_tree (aid, name, current_level, doj) VALUES (1, 'name1', 0, '2011-07-13')

Child node inserting values :


INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (2, 'name2', 1, 0, 1, '2011-07-31')
INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (3, 'name3', 1, 1, 1, '2011-07-31')
INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (4, 'name4', 2, 0, 2, '2011-08-01')
INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (5, 'name5', 2, 1, 2, '2011-08-02')
INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (6, 'name6', 3, 0, 2, '2011-08-01')
INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (7, 'name7', 3, 1, 2, '2011-08-02')

Finally We got the following Tree Structure



                                                    1 
                                     (Date of Joining : 2011-07-13)
									 
			2				                               3
	       (doj:2011-07-31)					             (doj:2011-07-31)
			
			
		4                     5                               6                      7		
(doj:2011-08-01)		(doj:2011-08-01) 	(doj:2011-08-01)		(doj:2011-08-02)

I use the following code



declare @nodeid int = 1
;with cte as (
       select
               aid, parent_aid, position,
               null lnode,
               null rnode
       from binary_tree where aid = @nodeid
       union all
       select
               t.aid, t.parent_aid, t.position,
               ISNULL(cte.lnode, CASE WHEN t.position = 0 THEN 1 ELSE 0 END) lnode,
               ISNULL(cte.rnode, CASE WHEN t.position = 1 THEN 1 ELSE 0 END) rnode
       from binary_tree t
       inner join cte
               on cte.aid = t.parent_aid
)
select
       @nodeid aid,
       SUM(lnode) LeftNodes,
       SUM(rnode) RightNodes
from cte

It counts left and right node very fine.

No we get @nodeid=1 node left and right count is : 3:3 likewise all availble child nodes.

But i want count node using Date of Joining [i.e, I want get the right and left node count in a particular day]

so i am changed the above code like below...



declare @nodeid int = 1
;with cte as (
       select
               aid, parent_aid, position,
               null lnode,
               null rnode
       from binary_tree where aid = @nodeid
       union all
       select
               t.aid, t.parent_aid, t.position,
               ISNULL(cte.lnode, CASE WHEN t.position = 0 THEN 1 ELSE 0 END) lnode,
               ISNULL(cte.rnode, CASE WHEN t.position = 1 THEN 1 ELSE 0 END) rnode
       from binary_tree t
       inner join cte
               on cte.aid = t.parent_aid AND doj = '2011-08-01'
)
select
       @nodeid aid,
       SUM(lnode) LeftNodes,
       SUM(rnode) RightNodes
from cte

it shows left and right count is null but insted of using this doj = '2011-07-31' it shows left and right node count 1:1....

and also i want to get left and right child name list in a particular give node....

please help me...

thanks in advance...

Till I am Live!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-02 : 10:35:38

[code]
Declare @t TABLE (
aid bigint UNIQUE,
name varchar(150) NOT NULL,
parent_aid bigint NULL,
position char(1) NULL,
current_level int NULL,
doj varchar(20) NOT NULL,
status INT NOT NULL DEFAULT '1'
)

INSERT INTO @t (aid, name, current_level, doj) VALUES (1, 'name1', 0, '2011-07-13')

INSERT INTO @t (aid, name, parent_aid, position, current_level, doj) VALUES (2, 'name2', 1, 0, 1, '2011-07-31')
INSERT INTO @t (aid, name, parent_aid, position, current_level, doj) VALUES (3, 'name3', 1, 1, 1, '2011-07-31')
INSERT INTO @t (aid, name, parent_aid, position, current_level, doj) VALUES (4, 'name4', 2, 0, 2, '2011-08-01')
INSERT INTO @t (aid, name, parent_aid, position, current_level, doj) VALUES (5, 'name5', 2, 1, 2, '2011-08-02')
INSERT INTO @t (aid, name, parent_aid, position, current_level, doj) VALUES (6, 'name6', 3, 0, 2, '2011-08-01')
INSERT INTO @t (aid, name, parent_aid, position, current_level, doj) VALUES (7, 'name7', 3, 1, 2, '2011-08-02')

declare @nodeid int = 1

;with cte as (
Select aid, parent_aid, position, doj, lnode= null, rnode = null
From @t
Where aid = @nodeid
Union All
Select t.aid, t.parent_aid, isnull(cte.position,t.position), t.doj,
lnode = CASE WHEN isnull(cte.position,t.position) = 0 and t.doj = '2011-08-01' THEN 1 ELSE 0 END,
rnode = CASE WHEN isnull(cte.position,t.position) = 1 and t.doj = '2011-08-01' THEN 1 ELSE 0 END
From @t t
Inner Join cte
On t.parent_aid = cte.aid
)
select --* From cte
@nodeid aid,
SUM(lnode) LeftNodes,
SUM(rnode) RightNodes
from cte
[/code]

Corey

I Has Returned!!

talktosivas
Starting Member

6 Posts

Posted - 2011-08-02 : 10:58:19

quote:

Originally posted by Seventhnight


declare @nodeid int = 1

;with cte as (
	Select aid, parent_aid, position, doj, lnode= null, rnode = null
	From @t
	Where aid = @nodeid
	Union All
	Select t.aid, t.parent_aid, isnull(cte.position,t.position), t.doj, 
		lnode = CASE WHEN isnull(cte.position,t.position) = 0 and t.doj = '2011-08-01' THEN 1 ELSE 0 END,
		rnode = CASE WHEN isnull(cte.position,t.position) = 1 and t.doj = '2011-08-01' THEN 1 ELSE 0 END
	From @t t
	Inner Join cte
	On t.parent_aid = cte.aid
)
select --* From cte
       @nodeid aid,
       SUM(lnode) LeftNodes,
       SUM(rnode) RightNodes
from cte

Wow thank you very much Seventhnight....
how to get left and right node name left... for example if we give node id 1 means...


Left Side                           Right Side

aid    name                        aid     name    
---    -------                     ---     ------- 
2      name2                        3       name3
4      name4                        6       name6
5      name5                        7       name7

once again really very thanks my dear friend Seventhnight

Till I am Live!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-02 : 11:05:54

quote:

Originally posted by talktosivas
how to get left and right node name left... for example if we give node id 1 means...


Left Side                           Right Side

aid    name                        aid     name    
---    -------                     ---     ------- 
2      name2                        3       name3
4      name4                        6       name6
5      name5                        7       name7

It'd be almost the same query:


declare @nodeid int = 1

;with cte as (
	Select aid, parent_aid, position, side = convert(varchar(10),null), name
	From @t
	Where aid = @nodeid
	Union All
	Select t.aid, t.parent_aid, position = isnull(cte.position,t.position),
		convert(varchar(10),case isnull(cte.position,t.position) when 0 then 'Left' when 1 then 'Right' end),
		t.name 
	From @t t
	Inner Join cte
	On t.parent_aid = cte.aid
)
select *
from cte
Where side is not null
Order by side

Corey

I Has Returned!!

talktosivas
Starting Member

6 Posts

Posted - 2011-08-02 : 11:19:25

Wow hello Seventhnight,...

Thank you very much my dear friend... very quick reply my post...

i want ask one more thing its a efficient and performance query for executing more than 50K records in binary_tree table

If 50K records in table binary_tree, whether to display left and right node name displayed quickly?

i am asking any performance tuning necessary to this queries! i am new to MS SQL so only i am asking do not mistake me please!

once again really great thanks to you Seventhnight! You are a Man!

Till I am Live!

talktosivas
Starting Member

6 Posts

Posted - 2011-08-02 : 13:48:31

quote:

Originally posted by Seventhnight

quote:

Originally posted by talktosivas
how to get left and right node name left... for example if we give node id 1 means...


Left Side                           Right Side

aid    name                        aid     name    
---    -------                     ---     ------- 
2      name2                        3       name3
4      name4                        6       name6
5      name5                        7       name7

It'd be almost the same query:


declare @nodeid int = 1

;with cte as (
	Select aid, parent_aid, position, side = convert(varchar(10),null), name
	From @t
	Where aid = @nodeid
	Union All
	Select t.aid, t.parent_aid, position = isnull(cte.position,t.position),
		convert(varchar(10),case isnull(cte.position,t.position) when 0 then 'Left' when 1 then 'Right' end),
		t.name 
	From @t t
	Inner Join cte
	On t.parent_aid = cte.aid
)
select *
from cte
Where side is not null
Order by side

To changing the nodeid value to 2 means the query not working fine...


aid                  parent_aid           position side       name
-------------------- -------------------- -------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
4                    2                    0        Left       name4
5                    2                    0        Left       name5

(2 row(s) affected)

Till I am Live!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-02 : 14:33:25

Need the bit in red

quote:

Originally posted by talktosivas

quote:

Originally posted by Seventhnight

quote:

Originally posted by talktosivas
how to get left and right node name left... for example if we give node id 1 means...


Left Side                           Right Side

aid    name                        aid     name    
---    -------                     ---     ------- 
2      name2                        3       name3
4      name4                        6       name6
5      name5                        7       name7

It'd be almost the same query:


declare @nodeid int = 1

;with cte as (
	Select aid, parent_aid, position=null, side = convert(varchar(10),null), name
	From @t
	Where aid = @nodeid
	Union All
	Select t.aid, t.parent_aid, position = isnull(cte.position,t.position),
		convert(varchar(10),case isnull(cte.position,t.position) when 0 then 'Left' when 1 then 'Right' end),
		t.name 
	From @t t
	Inner Join cte
	On t.parent_aid = cte.aid
)
select *
from cte
Where side is not null
Order by side

To changing the nodeid value to 2 means the query not working fine...


aid                  parent_aid           position side       name
-------------------- -------------------- -------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
4                    2                    0        Left       name4
5                    2                    0        Left       name5

(2 row(s) affected)

Till I am Live!

Corey

I Has Returned!!

talktosivas
Starting Member

6 Posts

Posted - 2011-08-02 : 16:14:32

quote:

Originally posted by talktosivas

quote:

Originally posted by Seventhnight


declare @nodeid int = 1

;with cte as (
	Select aid, parent_aid, position, doj, lnode= null, rnode = null
	From @t
	Where aid = @nodeid
	Union All
	Select t.aid, t.parent_aid, isnull(cte.position,t.position), t.doj, 
		lnode = CASE WHEN isnull(cte.position,t.position) = 0 and t.doj = '2011-08-01' THEN 1 ELSE 0 END,
		rnode = CASE WHEN isnull(cte.position,t.position) = 1 and t.doj = '2011-08-01' THEN 1 ELSE 0 END
	From @t t
	Inner Join cte
	On t.parent_aid = cte.aid
)
select --* From cte
       @nodeid aid,
       SUM(lnode) LeftNodes,
       SUM(rnode) RightNodes
from cte

Wow thank you very much Seventhnight....
how to get left and right node name left... for example if we give node id 1 means...


Left Side                           Right Side

aid    name                        aid     name    
---    -------                     ---     ------- 
2      name2                        3       name3
4      name4                        6       name6
5      name5                        7       name7

once again really very thanks my dear friend Seventhnight

Till I am Live!

Hai once again in this query @nodeid int = 2 not working properly ya.. how to change isnull to null??

Like this...


Select t.aid, t.parent_aid, position = null(cte.position,t.position), t.doj,

the above code is correct ah?

please help me...

Till I am Live!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-03 : 06:35:56

What is incorrect? Did you run the corrected query? What should the results of @nodeId = 2 look like?

Corey

I Has Returned!!

talktosivas
Starting Member

6 Posts

Posted - 2011-08-03 : 12:13:48

Hello friend,

it only calculate root node's left and right nodes names


declare @nodeid int = $aid
;with cte as (
Select aid, parent_aid, position=null, side = convert(varchar(10),null), name
From binary_tree
Where aid = @nodeid
Union All
Select t.aid, t.parent_aid, isnull(cte.position,t.position),
convert(varchar(10),case isnull(cte.position,t.position) when 0 then 'Left' when 1 then 'Right' end),
t.name 
From binary_tree t
Inner Join cte
On t.parent_aid = cte.aid
)
select *
from cte
Where side is not null
Order by side

I think its work.. please suggest me...
Thanks a lot Seventhnight....

Till I am Live!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources