[IMG]C:\Documents and Settings\oOo\De sktop\Introduce r Table.jpg[/IMG] Hi all.
I have a table in sql 2005 management studio express edition. The table consists of three columns: IntroducerID, LeftCustomerID and RightCustomerID .
And the table consists of hierarchical data, exactly like a binary tree.
LeftCustomerID and RightCustomerID can become IntroducerID and have left and right child and this continues to create a hierarchical parent child relationship in the table.
Now I need to count the number of left and right child in the table when i pass an IntroducerID. A simple select statement just returns three rows.
Here is what I have written. It is a stored procedure.
Create Procedure dbo.StoredProce dure1
(
@parentID int
)
Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer
WHERE (IntroducerID=@ parentID OR
IntroducerID=(S elect LeftCutomerID FROM Introducer WHERE IntroducerID=@p arentID) OR
IntroducerID=(S elect RightCutomerID FROM Introducer WHERE IntroducerID=@p arentID) );
The @parentID will be one of the IntroducerID in the Introducer table.
Please help me. I m in urgent. Thanks in advance.