creating Stored Procedure

Question

Hello Friend !!!

I am working on a MLM Project which is following the Binary System that is each user have to create 2 child users. Now I want to create a stored procedure which will insert  the user in the database. i have two tables:

userdetails: Master Table Which contains the records of all the employes structure:

ID (Auto-increment Primary key) username password and other details

UserRelation Table (Child Table) Structure
ID (Auto-increment Primary key) UserID(FK from Userdetals table stores the id of the current user being inserted) ParentUserID(FK from userdetails stores the ID of the user under which the current user is being inserted) position (L or R indicates where the new or current user is being inserted) referaluserid (null if the parentid is provided, and if the refralid is provided then the record needs to inserted like we have to find the parentid. )

for e.g.

A has Childs B(left) C(Right)
B has Childs D(left) E(Right)
C has Childs F(left)

Now if the new user G gives parent id as C then it will get inserted at C's right position but if G gives refral id of A then i have to find the parentid as i have to go through all the n-level (till 12th level) of records for A and find where i  need to insert the new user G First the left side will be precessed if the legs are balanced else the dis-balanced leg will get this entry.
 

Sorry to bother you but i need it urgently my boss want me to create this sp by today evening :( no clues where i am going :(

---

Thanks and Regards
Meetu Choudhary
Do not forgot to Mark as answer if it helps you My Blog||MsDnM||MsDnH

Answer 1

Hello,

To find out the Parent ID you need to wirte a recursive CTE,

Please search with text 'Recursive CTE' you will find so many links, best example is there in MSDN link

---

Thanks & Regards Prasad DVR

Answer 2

Hi Meetu,

For this, you need to add two more columns of VARCHAR type in your child table - Path and ParentPath.

Path will be a computed column - ParentPath + CONVERT(VARCHAR,ID) + '/'

ParentPath column will contain the value of Path column of its Parent.

Now, when you have the refralID, just search it using LIKE operator in the Path column and get the complete hierarchy and filter the node not having either LEFT or RIGHT member with the minimum LEVEL. This will give the exact place you need to add the new member.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia