count total paired members of the tree by sqlquery

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I am having a table tbltestingtree representing the tree structure, Member_Id is the id of the member, Parent_Id represent the Parentid of that member , Lefts and Rights tells us that the member is attached to the left or right of the Parent.
My problem is that i have to make a sqlquery that counts the total number of pared members for any particular parent

Example -  currently member with id 1 has 3 pairs with id 2,3 and 4  
           currently member with id 2 has 1 pair with id  4

Member_Id     Parent_Id      Lefts         Rights
   1              Null       Null          Null
   2                1          1            Null
   3                1         Null           1
   4                2          1            Null
   5                2         Null           1
   6                3          1            Null
   7                3         Null           1
   8                4          1            Null
   9                4         Null           1

Image of tree is here
http://imageshack.us/content_round.php?page=done&l=img4/4605/65ai.jpg[^]

Posted 3-Oct-13 4:27am

ashumeerut

Updated 4-Oct-13 0:00am

v2

Add a Solution

Comments

saguptamca 3-Oct-13 10:35am

didnt get u, plz explain more. wht u need

ashumeerut 3-Oct-13 11:02am

sir, If we talk about memberid 1 then its paired children will be 2,3,4 so the answer will be counting them that is 3, but if i talk about memberid 2 then its paired children will be member with id 4 because it is the only child of member 2 which is paired so its answer will be 1, i want such type of query

Azee 3-Oct-13 13:35pm

how is 4 related to 1 in your first example?
Sorry it seems a little confusing.

ashumeerut 4-Oct-13 0:26am

even 6 is related to 1 but we will not count it as it is not paired means it doesnot have nodes attached to its left and right.

Azee 4-Oct-13 1:08am

So according to the table, 1 has 2 and 3 attached to its left and right. not 4

ashumeerut 4-Oct-13 2:21am

yah,according to the table, 1 has 2 and 3 attached to its left and right. not 4, but we have to count 4 also, because it comes below 1 and 4 is paired(having child at its left and right)

Azee 4-Oct-13 2:38am

So you want to calculate child nodes of the parent?

ashumeerut 4-Oct-13 2:46am

i want to calculate those child nodes which are paired(having child at its left and right) . i will give the memberid in the query and it will calculate those child nodes which are paired(having child at its left and right) for that memberid.

Raja Sekhar S 4-Oct-13 2:55am

Provide Sample input and Output...

ashumeerut 4-Oct-13 3:02am

input(memberid) output(total Paired Childrens below it)
1 3
2 1
3 0
4 0
5 0
6 0
7 0
8 0
9 0

2 solutions

Solution 2

Hey there,

I know its a late reply and it has alreadt been solved, but here is what I tried.

I created a function (sort of recursive) and called it a stored procedure.

Stored Procedure:

SQL

CREATE PROCEDURE GetPairsCount
	@member int,
	@Count int output
AS
BEGIN

	Set @Count = dbo.GetTreePairsMethod(@member);
	
END

Function:

SQL

create FUNCTION GetTreePairsMethod 
(
	@member int
)
RETURNS int
AS
BEGIN
	Declare @result int = 0;
	if(Select count(*) from tree where parent = @member and ((lefts is not null and rights is null) or (lefts is null and rights is not null) ) ) = 2
	begin
		set @result = @result + 1;
		set @result += (Select  SUM(dbo.GetTreePairsMethod(memeber)) from tree where parent = @member and ((lefts is not null and rights is null) or (lefts is null and rights is not null) ) )
	end
	return @result
END

Table fields may a little different, e.g, memeber for memeber_id, but if you pass a memberID to the stored procedure it gets you the number of pared members of that parent.

Azee...

Posted 5-Oct-13 7:01am

Azee

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ashumeerut · Accepted Answer · 2013-10-04T02:34:00

Finally I Got My Answer :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTestingTree](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ParentId] [int] NULL,
	[IsLeft] [bit] NULL,
	[IsRight] [bit] NULL,
 CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblTestingTree] ON
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL)
SET IDENTITY_INSERT [dbo].[tblTestingTree] OFF



declare @ParentId as int
set @ParentId=2

create table #temp_table_name
(
 ParentId varchar(30) null,
 )

  
;with Child as
(
	select id,ParentId from tblTestingTree where id=@ParentId
	union all
	Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree 
	inner join Child 
	on tblTestingTree.ParentId=Child.Id
)

insert into #temp_table_name
select c.ParentId  from tblTestingTree T join Child c
on c.Id=t.Id
WHERE ISNULL(T.ParentId, 0) <> 0 and c.ParentId!=@ParentId
group by c.ParentId
having COUNT(c.ParentId)>1 

select COUNT(*) from #temp_table_name

drop table #temp_table_name