I probably did not provide enough info the first time. The members table is setup as follows:
Id (primary key) Referer (relates to the primary key of the table) I am given a member ID, say 4. I need to display all members who have been referred by member 4 (obviously no problem). However, for each row that matches, I also need the number of members referred by THAT member. I tried a simple join, but it doesn't display members with 0 referrals, so I'm thinking I need to left join the table to itself. The query I initially posted: SELECT m1.username, m1.first_name, m1.email, DATE_FORMAT(m1.signup_date, '%b %e, %Y') AS signup_date, COUNT(m2.id) AS count FROM members AS m1 LEFT JOIN members AS m2 ON m1.referer = m2.id WHERE m1.referer = 4 GROUP BY m1.referer; Does not work. It returns only one row (should be 3)...the count at the end is 3, which is the total of all members referred by all the members that should be returned...leading me to think I'm close, just not quite there! Would appreciate any help. Thanks all =) --------------------------------------------------------------------- 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
