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 |
|
prajeeshkk
Starting Member
3 Posts |
 Posted - 2009-12-15 : 06:09:21
|
| Hi All,I am trying to develop a Multi level Marketing web application, here i used two tables to store customer data, here is the schema of the tables.1.First Table (TFUser) [User_Id] (Primery Key) ,[User_Product_Id] ,[User_ParentId] ,[User_Name] ,[User_DOB] ,[User_Status] ,[User_LogName] ,[User_Passwd]2.Second Table (TFUaccount) [UAccount_Id] ,[UAccount_User_Id] (Foreign key) The problem is , i would like to select number of childs in each depth of a tree when we pass user id to the stored procedure .For Example :The result must be like:User_Id Depth Child_Count 1 1 11 2 31 3 21 4 5 Can anybody help me , how to write stored procedure for this purpose? |
|
|
ddramireddy
Yak Posting Veteran
81 Posts |
Posted - 2009-12-15 : 09:27:32
|
| You need for particularly 1 UserId or for all UserIds?????For 1 UserId, below query will come handy..For all UserIds, we need to do some tweak..declare @UserId intset @UserId = 1declare @TFUser table( [User_ID] int, [User_ParentID] int null)insert into @TFUserselect 1,null union allselect 2,1 union allselect 3,1 union allselect 4,2 union allselect 5,null union allselect 6,5; with cte as( select [User_Id],1 as [Depth] from @TFUser where [User_ID] = @UserId union all select T.[User_Id],[Depth]+1 from cte c inner join @TFUser T on c.[User_Id] = T.[User_ParentId])select @UserId as UserId,[Depth],COUNT(*) as [Child_Count] from cte group by [Depth] |
 |
|
|
prajeeshkk
Starting Member
3 Posts |
Posted - 2009-12-15 : 12:02:19
|
| hi ddramireddy,My problem has been solved :)Thank you very much..Also can you show me , how can we display all user_id's |
|
|
|
|
|
|
|
|
Selecting count of childs in each level