SELECT COUNT(p.postid) AS numposts, u.* FROM bmb_posts p LEFT JOIN bmb_users u USING(userid) WHERE p.userid = u.userid AND u.name LIKE '" . $delimiter ."%' GROUP BY userid ORDER BY name ASC LIMIT 0,25
For some reason, if the user doesn't have any posts that reside in bmb_posts (marked by their userid in the table), then it doesn't show up the result.
Any way around this little problem?
EDIT: $delimiter is a letter (a, b, c, etc)
Last edited by phice on Wed Apr 07, 2004 11:12 pm, edited 2 times in total.
the way you have it will only return a user if they have posts, when there is nothing to match their userid to then no result, try selecting the user and joining their posts to them, try the below ...
SELECT COUNT(p.postid) AS numposts, u.* FROM bmb_users u LEFT JOIN bmb_posts p USING(userid) WHERE p.userid = u.userid AND u.name LIKE '" . $delimiter ."%' GROUP BY userid ORDER BY name ASC LIMIT 0,25
sorry, my heads on holiday, wish I could join it , since you are joining the tables on userid you dont need p.userid = u.userid, as p.userid wont exist when there are no post for that member it will exclude the results ...
SELECT COUNT(p.postid) AS numposts, u.* FROM bmb_users u LEFT JOIN bmb_posts p USING(userid) WHERE u.name LIKE '" . $delimiter ."%' GROUP BY userid ORDER BY name ASC LIMIT 0,25
SELECT COUNT(p.postid) AS numposts, u.* FROM bmb_users u LEFT OUTER JOIN bmb_posts p USING(userid) WHERE p.userid = u.userid AND u.name LIKE '" . $delimiter ."%' GROUP BY userid ORDER BY name ASC LIMIT 0,25
SELECT COUNT(p.postid) AS numposts, u.* FROM bmb_users u LEFT OUTER JOIN bmb_posts p ON p.userid = u.userid WHERE u.name LIKE '" . $delimiter ."%' GROUP BY userid ORDER BY name ASC LIMIT 0,25