Complecated SQL Query: Members' Downline Business SQL Query

Question

Hello Everyone! I've got confused at the moment.

I've got one database to developed for MLM (Multi level marketing) company. so, there's one Members database.. each member have unique ID called membercode.. and all members can members under them.. can make 2 members down to 1 member.. all members are added in same table named "tbMembers" and for each member there's a "parentID" where I add member's membercode under whome that member is?.. right?

Now what's the matter is .. I want a SQL Query which can go under and under.. like 'John Doe' having two members under him named 'Suzan' and 'Ellie' and 'Suzan' is also having 2 members under her.. and same for Ellie. and for example.. 'John Doe' having about 300 members in his down line? All members are making transactions.. like they purchase product and Invoices are being generated (invoices tables have 'memberID'). Now the query is how to generate query which can calculate John Doe's Downline's TurnOver from invoices table's 'totalAmount' field's SUM?

I'm using Asp.net 2.0 (VB.net) and use SQL Server 2005 Express. How to do it? Please help me out guyz.

Answer 1

You can use a Recursive Common Table Expression to get all descendants then join the results onto the invoices table. Something like the following.

;WITH Members AS
     ( SELECT membercode,
             parentID
     FROM    tbMembers
     WHERE   name = 'John Doe' /*Obviously Id would be better*/

     UNION ALL

     SELECT c.membercode,
            c.parentID
     FROM   tbMembers c
            JOIN Members m
            ON     m.membercode= c.parentID
     )


SELECT SUM(totalAmount) AS Amount
FROM   invoices i
       JOIN Members m
       ON     i.MemberId = m.membercode

Answer 2

I think you need to use recursive user defined function. Which will calculate the total sum.