Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to write sql query for counting pairs
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 02/22/2014 :  07:20:55  Show Profile  Reply with Quote

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


Edited by - randhawa_125 on 02/22/2014 07:30:04
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000