Thank you much - I somewhat understand the query that you posted now. The problem that I'm dealing with NOW however is that it will only display results if there is a second level referree. I need to get results despite whether there are six levels or referrals, or only one. I added a third level, and it only displays results for those with 3 levels. Can you think of a way to modify this statement?
Scalper wrote: > Check out http://www.dcs.napier.ac.uk/~andrew/sql/. (When it is > up!). Otherwise, just do a search on the web for self-joins. There > should be plenty of info out there. > > Craig > > At 12:44 PM 4/3/2002, you wrote: > >> Oh wow, Ok, I'm not familiar with self joins, so this is all pretty >> much greek to me :) >> Can you point me to some documentation possibly explaining self >> joins/inner joins? >> Thanks >> >> Scalper wrote: >> >>> 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
