how can i get all childs nodes from left and right of a particular parent

[srikrrishna]

i have 3 tables
1. regi

in that regi fields are

  id - sponserid - introducerid- name- position
   1-   null-      0 -           devi- o
   2 -  devi-      1-           kasi-  2
   3-   devi -     1-            subbu- 1 
   4.   kasi  -    2-            mahi-2
   5.   kasi-      2-             ram- 1
   6.   subbu-     3 -           lucky-1
   7.   subbu-    3-             harsha-2

2. position table

   pid -position
   1 -  right
   2 - left

3. sponsers table

   cid- name
   1 -  devi
   2-  kasi
   3- subbu

how can i get childs nodes from left and right of a particular parent.

here i used the save stored procedure in savere

ALTER PROCEDURE [dbo].[savere]
	@sponserid varchar(50),
	@introducerid int,
	@placedunderid varchar(50),
	@name varchar(50),
	@so varchar(50),
	@position int,
	@gender int,
	@occupation varchar(50),
	@dob varchar(50),
	@addr varchar(200),
	@area varchar(50),
	@city varchar(50),
	@district varchar(50),
	@state varchar(50),
	@pincode varchar(50),
	@email varchar(50),
	@landline varchar(50),
	@mobileno varchar(50),
	@pwd varchar(50),
	@pin varchar(50),
	@nominee varchar(50),
	@relation varchar(50),
	@age varchar(50),
	@bankpayeename varchar(50),
	@accno varchar(50),
	@bankname varchar(50),
	@branch varchar(50),
	@ifsc varchar(50)
	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	insert into re
	(
	sponserid ,
	introducerid ,
	placedunderid ,
	name ,
	so ,
	position ,
	gender ,
	occupation ,
	dob ,
	addr ,
	area ,
	city ,
	district ,
	state ,
	pincode ,
	email ,
	landline ,
	mobileno ,
	pwd ,
	pin ,
	nominee ,
	relation ,
	age ,
	bankpayeename ,
	accno ,
	bankname ,
	branch ,
	ifsc,
	createddate
	 )
	 values
	 (
	 @sponserid ,
	@introducerid ,
	@placedunderid ,
	@name ,
	@so ,
	@position ,
	@gender ,
	@occupation ,
	@dob ,
	@addr ,
	@area ,
	@city ,
	@district ,
	@state ,
	@pincode ,
	@email ,
	@landline ,
	@mobileno ,
	@pwd ,
	@pin ,
	@nominee ,
	@relation ,
	@age ,
	@bankpayeename ,
	@accno ,
	@bankname ,
	@branch ,
	@ifsc ,GETDATE()
	)
	
	declare @cid int
	set @cid =SCOPE_IDENTITY()
	
	insert into sponsers(cid,name)values(@cid,@name)
END

[Rabbit]

Please use code tags when posting code or formatted data. Second Warning.

The answer to this question is the same as the answer to your other thread about counting all the child nodes: https://bytes.com/topic/sql-server/a...tion-calculate.

You will need to write a complicated recursive query using CTE's or you will need to change the data structure to accommodate easier queries. The choice is up to you. You can get more detail about each approach in the links in your other thread.