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 expectedArtem Kuchin19 Feb
  • Re: Left join does not work with Count() as expectedOlaf Stein19 Feb
    • InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni19 Feb
      • Re: InnoDB - CREATE INDEX - Locks table for too longBaron Schwartz20 Feb
        • Re: InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni21 Feb
        • Re: InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni16 Mar
  • Re: Left join does not work with Count() as expectedPeter Brawley19 Feb
  • RE: Left join does not work with Count() as expectedMartin Gainty19 Feb