Page 1 of 1

[SOLVED] query not showing some results

Posted: Fri Apr 02, 2004 9:03 pm
by phice

Code: Select all

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)

Posted: Sat Apr 03, 2004 9:50 pm
by phice
Any help guys?

Posted: Mon Apr 05, 2004 3:41 am
by Wayne
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 ...

Code: Select all

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

Posted: Mon Apr 05, 2004 1:45 pm
by phice
Nope, it's doing exactly what it did before (limiting the results to the users without any posts).

Posted: Tue Apr 06, 2004 3:46 am
by Wayne
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 ...

Code: Select all

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

Posted: Tue Apr 06, 2004 12:03 pm
by lostboy
LEFT OUTER JOIN

Posted: Tue Apr 06, 2004 12:55 pm
by phice
Nope, not working.

Posted: Tue Apr 06, 2004 1:03 pm
by lostboy
Flip the table names around, I am assuning you want the users with or without posts...

try

Code: Select all

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

Posted: Wed Apr 07, 2004 2:35 pm
by phice
Bah, it's still not working.... This is odd.

Posted: Wed Apr 07, 2004 3:04 pm
by lostboy
try

Code: Select all

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

Posted: Wed Apr 07, 2004 11:12 pm
by phice
Fantastic! :D Works great.

Posted: Thu Apr 08, 2004 7:01 pm
by qads
cool, thanks :D