[SOLVED] query not showing some results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

[SOLVED] query not showing some results

Post 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)
Last edited by phice on Wed Apr 07, 2004 11:12 pm, edited 2 times in total.
Image Image
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

Any help guys?
Image Image
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

Nope, it's doing exactly what it did before (limiting the results to the users without any posts).
Image Image
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

LEFT OUTER JOIN
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

Nope, not working.
Image Image
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

Bah, it's still not working.... This is odd.
Image Image
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

Fantastic! :D Works great.
Image Image
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

cool, thanks :D
Post Reply