Not really a MySQL question, but... The way you have it now definitely won't work. Your select statement for getting the number of referrals for level one will always return a count of one -- because id is the primary key of your table. One way to do this, without listing all the referrals in a single table like you have below, is to use two tables.
create table customers ( cust_id int unsigned not null auto_increment primary key, [other relevant fields] ); create table referrals ( cust_id int unsigned not null, ref_id int unsigned not null, ref_level tinyint unsigned not null, primary key (cust_id, ref_level), index (ref_id, ref_level) ); This assumes that all your referrals are coming from existing customers, so the ref_id field in the referrals table is really just another cust_id from your customers table. If this is not correct, you may have to have a 3rd table to generate the correct ref_id's. With this setup, you can have up to 256 referral levels. If you need more, just change the datatype of the ref_level field. You can easily find the ref_id for a particular cust_id and ref_level using the primary key: SELECT ref_id FROM referrals WHERE cust_id=10 and ref_level=3; You can also easily find the number of referrals a given customer has generated for any particular level: SELECT COUNT(*) FROM referrals WHERE ref_id=10 and ref_level=5; Or you can get a complete breakdown of referrals for a given customer by grouping on the ref_level: SELECT COUNT(*), ref_level FROM referrals WHERE ref_id=10 GROUP BY ref_level; Or for everybody at once: SELECT COUNT(*), ref_id, ref_level FROM referrals GROUP BY ref_id, ref_level; Etc... Does this make sense? --jeff ----- Original Message ----- From: "Daren Cotter" <[EMAIL PROTECTED]> To: "MySQL Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, September 25, 2002 9:34 AM Subject: Multiple Referral Levels > 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). > > The first design I considered was: > > table: > id int(8) unsigned not null auto_increment, > referer int(8) unsigned null, > primary key (id), > key tbl_referer(referer)); > > What I need to be able to do is give a breakdown of > the # of members referred (on each level) for a > specific member...say, member 10. Getting the # of > referrals on level 1 is no problem: > > SELECT COUNT(*) FROM table WHERE id = 10 > > The second level isn't too tough either, using a > simple join. But what about when I get down to level > 4? Is it even possible to get this info in one query? > How about level 10? > > The only other thing I can think of doing is storing > not just the referer in the table, but something like: > > id > ref1 > ref2 > ref3 > ref4 > ref5 > etc... > > All of the logic would need to be taken care of during > member registration, and querying to find the number > of referrals on any given level for a member would be > simple. However, this method does not allow for easy > expansion of referral levels, which is what I want, > and is probably not the best way of doing things. > > Can anyone offer any insight? > > __________________________________________________ > Do you Yahoo!? > New DSL Internet Access from SBC & Yahoo! > http://sbc.yahoo.com > > --------------------------------------------------------------------- > 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
