Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-08 : 09:55:42
|
Sukhwinder writes "My problem is little complex. I need to know how can I update multiple records in a table depending on some condition and also how to perform depth first search. I have asked my on different forums but haven't found any solution.For example in a table called members there is column called commission and a column called parentid. Each member has some parentid Whenever a new member is added to the table he must have been referred by some other member(parent). So other(old) member becomes new member's parent. This is a business scheme used by most web sites these days to earn.Whenever new member is added his parent gets some % of commission. So this commission has to be added to parent members commission field. It doesn't end here. This will go up to suppose 10 levels. For example parent will get 20%--his parent will get 19%--his parent will get 18% and so on up to ten levels above or if there are less than 10 levels, up to last levelSecond part is to find a parent id for the new member. In This case a tree like structure is created where parent is at top of the tree. Only two members (called right leg and left leg) can be added directly below the member who is referencing a new member. For example member 1 has two child member2 and member3. Member1(left leg)------------(right leg) | | Member2 Member3 | Member 4 Member1 cannot add new member under him because both legs are already occupied.When a new member 5 is referenced (introduced) by 1 it can be added below 4 or 3(it is called spill over) Member 1 will get spill over benefit along with his regular commission because new member is added below his tree. Also each member above Member 4 (member2--member1 --his parent...and up to 10 levels or last level if 10 levels haven't been yet created) will get commission.So problem when adding a new member is finding a free slot under Member1. First search will be performed under left leg up to 10 levels and if all places are already occupied. Search on the right leg will be performed and here free place under Member 3 will be found. And new member member 5 will be added under member3 (i.e is his parent will be member3). And commission will have to be calculated and updated again for each parent.So this is the story. How this thing can be achieved using SQL Server 7.0. How depth first search can be performed on left side and then on the right side? I know stored procedures and triggers have to be used but I need some expert advice about how this can be done efficiently?" |
|
|
ssruprai
Starting Member
5 Posts |
Posted - 2002-04-08 : 19:05:55
|
| One thing I forgot to mention is that weekly commission has to be calculated for each member. So another table commission has also to be created where dates can be added.Example of sites where things like these are used is http://www.bigbanyantree.com and http://ltwebcarrier.com and 100's more. Where every person can view his tree and view his commission etc.Also when searching for a free slot. I only told you about search which is like this: member1 / / / (left)/ \ (right) / / / / I forgot to mention that search for free slot also has to be performed like this where every node under member1 will be searched going left to right down to level 10. Because each member under member1 will also have left and right leg. member1 / / \ /\ /\ (left) /\ /\ /\ \ (right) / / / / / / /\ For example in first case when a new member member9 is referenced by member1 search for free slot will be made like this.Start from left leg of member1...member2 --occupiedmember4 --occupiedmember8 --occupied member1 ------------------------ | | member2 member3 | | | | member4 member5 member6 member7 | | | | member8 | |Now slot on left and right of member8 is free. So new member9 will be added under left leg of member8.In second case if there was no free slot under member8 and upto level 10 then member2's right and then member4's right and 8's right and if still unable to find; member5's left and member5's child's left leg..then member5's right and then member5's child's right and so on. It means we have to search each and every branch of the tree to find a free slot. Going left to right starting at member1.It is more difficult than searching only left and right of member1.Problem is while searching how do I keep track of where I am and when one side search is complete how to start again and from where when each and every member's right and left legs below member1 have to be searched.One person told me that he has achieved this without even using sql server and only using MSAccess (http://www.ltwebcarrier.com)If you can please suggest a way to search this tree like structure using SQL. |
 |
|
|
|
|
|
updating multiple rows, performing depth first seach