| List: | General Discussion | « Previous MessageNext Message » | |
| From: | Artem Kuchin | Date: | February 19 2009 4:09pm |
| Subject: | Left join does not work with Count() as expected | ||
| View as plain text | |||
I have two simple tables. One - list of forums, second - list of messages and i want to get the list of forums with number of messages in each. Here is the query: SELECT forums.id , COUNT( forum_msg.id ) AS cnt FROM forums LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id ORDER BY forums.sorder ASC The problem is that if a forum does not have any messages then the line with such forums.id does not appear at all. If i delete COUNT( forum_msg.id ) AS cnt from Select - i get all forums, as expected. If i leave the count() as shown - i get only forums with messages in the result. As far as i can remember it was not like this before. I am running 5.1.3 -- Artem Kuchin
| Thread | ||
|---|---|---|
| • Left join does not work with Count() as expected | Artem Kuchin | 19 Feb |
| • Re: Left join does not work with Count() as expected | Olaf Stein | 19 Feb |
| • InnoDB - CREATE INDEX - Locks table for too long | Claudio Nanni | 19 Feb |
| • Re: InnoDB - CREATE INDEX - Locks table for too long | Baron Schwartz | 20 Feb |
| • Re: InnoDB - CREATE INDEX - Locks table for too long | Claudio Nanni | 21 Feb |
| • Re: InnoDB - CREATE INDEX - Locks table for too long | Claudio Nanni | 16 Mar |
| • Re: Left join does not work with Count() as expected | Peter Brawley | 19 Feb |
| • RE: Left join does not work with Count() as expected | Martin Gainty | 19 Feb |
