Hello Friends,

I've a table as given below

ID LChild RChild
1 2 3
2 4 6
3 5 7
4 8 9
5 10 11
6 12 13
7 0 14


Now i've to find out the LChild of 1, 1 has 2 in it's left and 2 has 4,6 as it's child and 4 and 6 has also two-two child each......At final 1 has 7 child on it's left and on it's Right it's 6

And also i've find out for each ID i.e 1,2,3,4,5,6,7

This is a tough thing to do in T-SQL in SQL Server 2000 (and maybe 2005?? don't remember) because you need to use recursion, and 2000 doesn't support recursion.

SQL Server 2008 supports recursion, so it should be possible there.

What DB are you using?? 2000? 2005? 2008?

Oh...and if you don't HAVE to do this in SQL--if you can do it in ASP or ASP.NET code after getting the data into memory--then it's pretty easy.

Manoj:

It is not clear as to what you are trying to accomplish. From your example, are you wanting to know which ID has no LChild or RChild, as in a Btrieve Data Structure? If so, then you need to identify the Nodes, as in Root and /or Parent Nodes.

__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.

I'm using Sql Server 2005

I read it as his saying "I want to find the last LEFT node *AND* the last RIGHT node in the chains descending from each parent node."

His example data is far from complete, so it's hard to be sure that's what he meant, but I think that's the idea.

A better example:
So:
for node 1, the left chain is 1=>4=>5 and the right chain is 1=>6=>7=>9
for node 2, the left chain is 2=>3 and the right chain is 2=>8
for node 3, the left chain is just 3 and the right chain is just 3
etc.

So the results returned would be:
But of course I could be way off.

And does it *HAVE* to be done in SQL??? It would be *MUCH* easier and faster to do it in ASP.NET or ASP code, if that's an option.

Or Java, or C++, or C#, or VB.NET... choose your language (it's the SQL forum old pedant... OP could be using anything)...

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

LOL! Yes, of course. But he said he's using SQL Server, so I [made an *** out of...]d

Hey, I *did* say ASP.NET, so I did cover C# and VB.NET (and J# and ...).

Thanks Old Pendant for clearing that up. So in orde to find the Last Left and Last Right Nodes, the Left Child and Right Child would have to be in a certain order: For example, beginning with the 1-ID as the Paremt Node (PN) and "Less than" the parent node is listeddto the left of Parent Node and any child Node greater than the Parent Node is to the Right of the Parent Node.For Brevity sake, after 4 levels of Nodes, a Chhild Node can replace the parent node.
[code]
1=Parent Node .
< > 2
>3
> 6
<5 >7
Hope this did not confuse you. Regardless of which language, the idea is to generate the utility or tool that traverse the structukre.

__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.