Following the documentation available at
http://dev.mysql.com/doc/refman/5.0/en/join.html
t1 t2
---- ----
a b a c
--- ---
1 x 2 z
2 y 3 w
Then a natural left join would product these results
mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | x | NULL |
| 2 | y | z |
+------+------+------+
A Left join would produce these results)
(Row1 is included but 3 is leftout because it doesnt have a corresponding row
in t1)
mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a | b | a | c |
+------+------+------+------+
| 1 | x | NULL | NULL |
| 2 | y | 2 | z |
+------+------+------+------+
(Row1 is included but 3 is leftout because it doesnt have a corresponding row
in t1)
Your 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
assuming you have the data
Forums f Forum_msg fm
f.id f.cnt fm.id fm.cnt
------------- -----------------
1 1 2 2 2
2 2 3 3
YIELDS these results
---------------------------
if.id f.cnt fm.id fm.cnt
------ ----- ------ -------
1 1 NULL 0
2 2 2 2
Martin Gainty
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business
of Sender. This transmission is of a confidential nature and Sender does not
endorse distribution to any party other than intended recipient. Sender does
not necessarily endorse content contained within this transmission.
> Date: Thu, 19 Feb 2009 19:09:04 +0300
> From: [email protected]
> To: [email protected]
> Subject: Left join does not work with Count() as expected
>
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[email protected]
>
_________________________________________________________________
See how Windows connects the people, information, and fun that are part of your
life.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
