SQL Server - how to write sql query for counting pairs from below table??

Asked By Priyanka on 22-Feb-14 07:21 AM
Below is my SQL table structure.

user_id  |   Name   |   join_side   |  left_leg   |    right_leg    |   Parent_id

100001      Tinku          Left           100002        100003              0
100002     Harish         Left            100004        100005          100001
100003     Gorav         Right           100006        100007          100001
100004      Prince         Left             100008        NULL             100002
100005      Ajay           Right           NULL           NULL            100002
100006      Simran        Left             NULL           NULL            100003
100007      Raman    Right      NULL           NULL            100003
100008      Vijay      Left      NULL         NULL     100004

It is a binary table structure.. Every user has to add two per id under him, one is left_leg and second is right_leg... Parent_id is under which user current user is added.. Hope you will be understand..

I have to write sql query for counting pairs under id "100001". i know there will be important role of parent_id for counting pairs. * what is pair( suppose if any user contains  both left_leg and right_leg id, then it is called pair.)
I know there are three pairs under id "100001" :-
1.  100002 and 100003
2.   100004 and 100005
3.   100006 and 100007
      100008 will not be counted as pair because it does not have right leg..

 But i dont know how to write sql query for this... Any help will be appreciated... This is my college project... And tommorow is the last date of submission.... Hope anyone will help me...

Suppose i have to count pair for id '100002'. Then there is only one pair under id '100002'. i.e 100004 and 100005
Mayank Tripathi replied to Priyanka on 26-Feb-14 04:51 AM
Try the below query... hope this will help you.
To better understand I have created a table and inserted records as mentioned.
Did not get the importance of Join_Side column.

--CREATE TABLE dbo.BinaryStructure
--(
--    [User_ID] INT,
--    [Name] VARCHAR(50),
--    Join_Side CHAR(6),
--    Left_Leg INT,
--    Right_Leg INT,
--    Parent_ID INT
--)

--INSERT INTO dbo.BinaryStructure
--          SELECT 100001,'Tinku','Left',100002,100003,0
--UNION ALL SELECT 100002,'Harish','Left',100004,100005,100001
--UNION ALL SELECT 100003,'Gorav','Right',100006,100007,100001
--UNION ALL SELECT 100004,'Prince','Left',100008,NULL,100002
--UNION ALL SELECT 100005,'Ajay','Right',NULL,NULL,100002
--UNION ALL SELECT 100006,'Simran','Left',NULL,NULL,100003
--UNION ALL SELECT 100007,'Raman','Right',NULL,NULL,100003
--UNION ALL SELECT 100008,'Vijay','Left',NULL,NULL,100004

--INSERT INTO dbo.BinaryStructure
--          SELECT 100009,'Suresh','Left',100010,100011,0
--UNION ALL SELECT 100010,'Navin','Left',NULL,NULL,100004
--UNION ALL SELECT 100011,'Ramesh','Left',NULL,NULL,100004
--UNION ALL SELECT 100012,'KAmlesh','Left',100013,100014,100010
--UNION ALL SELECT 100013,'KAmlesh','Left',NULL,NULL,100010
--UNION ALL SELECT 100014,'KAmlesh','Left',NULL,NULL,100010

--SELECT * FROM dbo.BinaryStructure

DECLARE @User_ID INT
SET @User_ID = 100002 --100001

DECLARE @TempTable TABLE ([User_ID] INT, Left_Leg INT, Right_Leg INT, Parent_ID INT)
INSERT INTO @TempTable
SELECT [User_ID] , Left_Leg , Right_Leg , Parent_ID
FROM  dbo.BinaryStructure A
WHERE Left_Leg > 0 AND Right_Leg > 0
AND ([User_ID] = @User_ID OR PArent_ID = @User_ID)

SELECT COUNT(1) PairCount FROM @TempTable


Thanks and Regards,
Mayank Tripathi