By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

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

P: 4
i have 3 tables
1. regi

in that regi fields are
Expand|Select|Wrap|Line Numbers
  1.   id - sponserid - introducerid- name- position
  2.    1-   null-      0 -           devi- o
  3.    2 -  devi-      1-           kasi-  2
  4.    3-   devi -     1-            subbu- 1 
  5.    4.   kasi  -    2-            mahi-2
  6.    5.   kasi-      2-             ram- 1
  7.    6.   subbu-     3 -           lucky-1
  8.    7.   subbu-    3-             harsha-2
2. position table
Expand|Select|Wrap|Line Numbers
  1.    pid -position
  2.    1 -  right
  3.    2 - left
  4.  
3. sponsers table

Expand|Select|Wrap|Line Numbers
  1.    cid- name
  2.    1 -  devi
  3.    2-  kasi
  4.    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

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[savere]
  2.     @sponserid varchar(50),
  3.     @introducerid int,
  4.     @placedunderid varchar(50),
  5.     @name varchar(50),
  6.     @so varchar(50),
  7.     @position int,
  8.     @gender int,
  9.     @occupation varchar(50),
  10.     @dob varchar(50),
  11.     @addr varchar(200),
  12.     @area varchar(50),
  13.     @city varchar(50),
  14.     @district varchar(50),
  15.     @state varchar(50),
  16.     @pincode varchar(50),
  17.     @email varchar(50),
  18.     @landline varchar(50),
  19.     @mobileno varchar(50),
  20.     @pwd varchar(50),
  21.     @pin varchar(50),
  22.     @nominee varchar(50),
  23.     @relation varchar(50),
  24.     @age varchar(50),
  25.     @bankpayeename varchar(50),
  26.     @accno varchar(50),
  27.     @bankname varchar(50),
  28.     @branch varchar(50),
  29.     @ifsc varchar(50)
  30.  
  31.  
  32. AS
  33. BEGIN
  34.     -- SET NOCOUNT ON added to prevent extra result sets from
  35.     -- interfering with SELECT statements.
  36.     SET NOCOUNT ON;
  37.  
  38.     -- Insert statements for procedure here
  39.     insert into re
  40.     (
  41.     sponserid ,
  42.     introducerid ,
  43.     placedunderid ,
  44.     name ,
  45.     so ,
  46.     position ,
  47.     gender ,
  48.     occupation ,
  49.     dob ,
  50.     addr ,
  51.     area ,
  52.     city ,
  53.     district ,
  54.     state ,
  55.     pincode ,
  56.     email ,
  57.     landline ,
  58.     mobileno ,
  59.     pwd ,
  60.     pin ,
  61.     nominee ,
  62.     relation ,
  63.     age ,
  64.     bankpayeename ,
  65.     accno ,
  66.     bankname ,
  67.     branch ,
  68.     ifsc,
  69.     createddate
  70.      )
  71.      values
  72.      (
  73.      @sponserid ,
  74.     @introducerid ,
  75.     @placedunderid ,
  76.     @name ,
  77.     @so ,
  78.     @position ,
  79.     @gender ,
  80.     @occupation ,
  81.     @dob ,
  82.     @addr ,
  83.     @area ,
  84.     @city ,
  85.     @district ,
  86.     @state ,
  87.     @pincode ,
  88.     @email ,
  89.     @landline ,
  90.     @mobileno ,
  91.     @pwd ,
  92.     @pin ,
  93.     @nominee ,
  94.     @relation ,
  95.     @age ,
  96.     @bankpayeename ,
  97.     @accno ,
  98.     @bankname ,
  99.     @branch ,
  100.     @ifsc ,GETDATE()
  101.     )
  102.  
  103.     declare @cid int
  104.     set @cid =SCOPE_IDENTITY()
  105.  
  106.     insert into sponsers(cid,name)values(@cid,@name)
  107. END
Nov 13 '15 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
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.
Nov 13 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.