From: Martin Gainty Date: February 19 2009 6:17pm Subject: RE: Left join does not work with Count() as expected List-Archive: http://lists.mysql.com/mysql/216382 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_d099c9d4-187c-4539-b7b6-738a40543bbd_" --_d099c9d4-187c-4539-b7b6-738a40543bbd_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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=3B +------+------+------+ | 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 r= ow in t1) mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a =3D t2.a)=3B +------+------+------+------+ | 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 r= ow in t1) Your query SELECT forums.id =2C COUNT( forum_msg.id ) AS cnt FROM forums LEFT JOIN forum_msg ON forums.id =3D 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 ______________________________________________=20 Disclaimer and confidentiality note=20 Everything in this e-mail and any attachments relates to the official busin= ess of Sender. This transmission is of a confidential nature and Sender doe= s not endorse distribution to any party other than intended recipient. Send= er does not necessarily endorse content contained within this transmission.= =20 > Date: Thu=2C 19 Feb 2009 19:09:04 +0300 > From: matrix@stripped > To: mysql@stripped > Subject: Left join does not work with Count() as expected >=20 > I have two simple tables. One - list of forums=2C second - list of=20 > messages and i want to > get the list of forums with number of messages in each. >=20 > Here is the query: >=20 > SELECT forums.id =2C COUNT( forum_msg.id ) AS cnt > FROM forums > LEFT JOIN forum_msg ON forums.id =3D forum_msg.forum_id > ORDER BY forums.sorder ASC >=20 > The problem is that if a forum does not have any messages then the line > with such forums.id does not appear at all. >=20 > If i delete COUNT( forum_msg.id ) AS cnt from Select - i get all forums= =2C > as expected. If i leave the count() as shown - i get only forums with=20 > messages > in the result. As far as i can remember it was not like this before. I=20 > am running > 5.1.3 >=20 > -- > Artem Kuchin >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped= om >=20 _________________________________________________________________ See how Windows connects the people=2C information=2C and fun that are part= of your life. http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/= --_d099c9d4-187c-4539-b7b6-738a40543bbd_--