You are on the right track that you don't want to modify the database just to add a "level". You also want to have your "levels" in one column/field so that you can use an index to search it quickly and easily.
You didn't mention whether someone can be referred on multiple levels, this will really dictate the structure. If one can only be referred on one level, then it's fairly simple: Members ------------- MemberID MemberName RefererID (links to another member record in same database) RefererLevel SELECT COUNT(*) FROM Members WHERE RefererID=10 AND RefererLevel<4 That will give you all referrals for member 10 referred on level 1,2, or 3, but not 4. If one member can be referred multiple times on multiple levels, then you need to split things apart and query a little different. Members ------------- MemberID MemberName Referrals ------------- MemberID RefererID Level SELECT COUNT(DISTINCT(MemberID)) FROM Member WHERE RefererID=10 AND Level<4 That will give you all referrals for member 10 referred on level 1,2, or 3, but not 4. There is actually no need for a join unless you want to get the names of all the members that someone referred. Does that help or am I completely missing what you are trying to do? On Wednesday, September 25, 2002, at 12:34 PM, Daren Cotter wrote: > I have a question regarding tracking multiple referral > levels in a database. The number of referral levels > tracked needs to be at least 4, but should be able to > be expanded later (without modifying the database). -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 --------------------------------------------------------------------- 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
