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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
