On Sun, May 4, 2008 at 5:37 PM, Patrick Aljord <[EMAIL PROTECTED]> wrote: > > Your doing a left join which can increase the number of rows returned. > > This is then GROUP BYed and run through a HAVING. Is: > > posts.poster_id=users.id > > a one to one relationship? If it is not, then count(*) would be a > > larger number and pass the HAVING. This may not be your problem, but I > > suggest you have more than you realize. > > this is not a one-to-one as a user can make many comments but a > comment belongs to one user only, so I guess the left join returns > more than necesary. but I'm kind of stuck on that one :/ I said one to one, meant many to one (good) but not many to many (bad for this query). Sorry.Your join on something different than what you are grouping on. If you are getting more results look at what where the row count increases. If the only thing that changed was the join then you are getting more results because of it. I would investigate the table you are joining against.
> > I suggest reading > > http://www.xaprb.com/blog/2006/04/26/sql-coding-standards/ > > I read it thanks but I still can't find a way to do it right, I mean I > am following this pattern right no?: What came over was: 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 I also suggested reading a blog that he linked to about how to use GROUP BY. So a search for 'how to group data correctly in SQL'. > > select column ... > from table ... > where criterion ... > group by groupingclause ... > having havingclause ... > order by orderingclause ... > > Thanks in advance > > Pat > -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
