List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:February 19 2009 6:17pm
Subject:RE: Left join does not work with Count() as expected
View as plain text  
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: matrix@stripped
> To: mysql@stripped
> 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/mysql?unsub=1
> 

_________________________________________________________________
See how Windows connects the people, information, and fun that are part of your life.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
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