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)
 how to write sql query for counting pairs

Author  Topic 

randhawa_125
Starting Member

2 Posts

Posted - 2014-02-22 : 07:20:55

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
quote:

quote:

   

- Advertisement -