I have a table, which keeps track of member information (including which
member referred the member). To get a count of the # of referrals for member
25, my query is:
SELECT COUNT(*) FROM members WHERE ref1 = 25;
To get a list of the top referers and the # of referrals they have, my query
is:
SELECT DISTINCT(ref1) AS member_id, COUNT(*) AS count FROM members GROUP BY
ref1 ORDER BY count DESC LIMIT 100
However, what I need, is a list of the top referers, along with their member
information...name, email, password, etc. I tried using the following query,
as I read about it in the MySQL manual, but it doesn't work:
SELECT DISTINCT(a.ref1) AS member_id, count(*) AS count, b.password,
concat(UCASE(SUBSTRING(b.first_name,1,1)),
LCASE(SUBSTRING(b.first_name,2,LENGTH(b.first_name)))) AS name, b.email,
b.html_mail, b.ref1, DATE_FORMAT(b.signup_date, '%b %e, %Y') AS signup_date
FROM members AS a, members AS b WHERE a.active_member = 'Y' AND a.ref1 =
b.member_id GROUP BY a.ref1 ORDER BY count DESC LIMIT 10
This gives me correct info for the distinct a.ref1 and count fields, and
produces data for the rest of the fields, but it is not actually that
member's data. Is this possible to do with one query? If I want to get the
top 100 referers' data, I don't want to do 100 separate queries. Please
help!
Thanks,
Daren Cotter
---------------------------------------------------------------------
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
