The queries will be a little complicated using a self join, but the table is simple should include:
CUSTOMERID REFERRERID (Which is the CUSTOMERID of their referrer) CUSTOMERFIRSTNAME CUSTOMERLASTNAME ETC! The queries will then use a self join and table aliases to determine the commissions for each of the six levels. Using two levels would look something like this: SELECT CUSTOMERS.CustID, LEVEL1REFERRERS.CustID AS LEVEL1REFERREES, 1 AS LEVEL1COMMISSION, LEVEL2REFERRERS.CustID AS LEVEL2REFERREES, 1 AS LEVEL2COMMISSION FROM (CUSTOMERS INNER JOIN CUSTOMERS AS LEVEL1REFERRERS ON CUSTOMERS.CustID = LEVEL1REFERRERS.ReferrerID) INNER JOIN CUSTOMERS AS LEVEL2REFERRERS ON LEVEL1REFERRERS.CustID = LEVEL2REFERRERS.ReferrerID; If all you care about are the total commissions then again for two levels you would use: SELECT CUSTOMERS.CustID, Sum(1) AS LEVEL1COMMISSION, Sum(1) AS LEVEL2COMMISSION FROM (CUSTOMERS INNER JOIN CUSTOMERS AS LEVEL1REFERRERS ON CUSTOMERS.CustID = LEVEL1REFERRERS.ReferrerID) INNER JOIN CUSTOMERS AS LEVEL2REFERRERS ON LEVEL1REFERRERS.CustID = LEVEL2REFERRERS.ReferrerID GROUP BY CUSTOMERS.CustID; Obviously you would need to expand these to include the full six levels (whew!), but you could always create temporary heap tables to break this down into smaller manageable task. NOTE: I have not tested these queries so they may need some tweaking, but hopefully you get the general idea. Let me know if you want clarification. Craig sql,query At 07:46 PM 4/2/2002, you wrote: >Hi, I just now signed up for this list a second ago and thought I'd say >howdy to everyone. >I'm not sure what the typical discussions are like on this list, so I'll >go ahead and ask anyhow. >If there is another list that would be better suited to this type of >question, please let me know. > >A friend of mine who runs a small company has recently decided he's going >to award his customers >based on a 'referral' type program where, if I refer somebody to him, I >get a recurring check for say $1 a month. If the person I referred refers >somebody, that person get's $1 a month, and then I get say $0.50 a month >on top of my $1 a month. People can refer as many people as they'd like, >but for the 'sub-referrers' (i can't think of a better term to call them), >I would only get paid up to six degrees of the original referrer. >Hope that makes since... > >Anyhow, my friend has asked me to build a database where all of this >information could be stored, and then I would have a script determine what >is owed to who at the end of each month. The problem I'm having is >that... I just can't think of a table structure to even begin with. I can >do a simple table where I would have the customer_id, and then >referrer_id, but the script would have to do a massive amount of queries >and this just wouldn't be efficient at all. > >Can someone point me in the right direction as to how I should maybe set >up these tables? Thanks! > > >P.S. - This message originally was returned to me because I didn't have >the words >sql or query in them. Well, it does now :) What a weird filter. > > > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
