Re: why does left join gives more results?

["Brent Baisley" <brenttech@xxxxxxxxx>]

If you are getting more results, I would guess that your users table
is not a list of unique users, but a list of user logins. If that is
the case, then it's your LEFT JOIN and the count(*) that is causing
you to get more results.
If a user logs in 5 times, but only has 1 post, you will get 5 records
for that user out of the LEFT JOIN. That's the way left joins work.
You're then doing a count on all the records, but you really just want
a count of the number of posts.
If all my assumptions are correct, then a quick fix for your query is
to change your count(*) to this:
count(DISTINCT posts.post_id) as counted

That will count the number of unique posts. I don't know what your
unique field name is for the posts table.

Brent Baisley
Systems Architect

On Sat, May 3, 2008 at 9:00 PM, Patrick Aljord <patcito@xxxxxxxxx> wrote:
> hey all,
>
>  I have my query that counts posts per user:
>
>  SELECT count(*) as counted, c.user_id FROM posts c  group by c.user_id
>  having counted>1 order by counted DESC LIMIT 20
>
>  I wanted to add user login for each count so I did:
>
>  SELECT count(*) as counted, u.login FROM posts c left join users u on
>  posts.poster_id=u.id  group by c.user_id having counted>1 order by
>  counted DESC LIMIT 20
>
>  but now I get more results.
>
>  Any idea what I'm doing wrong?
>
>  Thanks in advance
>
>  Pat
>
>  --
>  MySQL General Mailing List
>  For list archives: http://lists.mysql.com/mysql
>  To unsubscribe:    http://lists.mysql.com/mysql?unsub=brenttech@xxxxxxxxx
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx