jxrd Posted June 13, 2009 Share Posted June 13, 2009 Hi all, I have this query, to get entries for my blog. Before now, I used to count comments in a seperate query, but thought I'd have a go at doing it using a join. This is what I have: "SELECT B.*, COUNT(C.`ID`) AS `CommentCount` FROM `$tb_Blog` B LEFT OUTER JOIN `$tb_Blog` C ON B.`ID`=C.`ID2` AND C.`Type`='comment' WHERE B.`Type`='entry' ORDER BY B.`ID` ASC" ID is the id of the entry/comment and ID2 is the field for comments that relates to its entry's ID. However, this only returns one entry with an incorrect amount of comments. I really don't understand why... Can anyone help? Thanks. Quote Link to comment Share on other sites More sharing options...
Maq Posted June 13, 2009 Share Posted June 13, 2009 Shouldn't you be using a plain LEFT JOIN, rather than an OUTER? Quote Link to comment Share on other sites More sharing options...
jxrd Posted June 13, 2009 Author Share Posted June 13, 2009 Hey Maq. I thought that without the outer join, if no comments are found, nothing would be returned? Idk, I'm not hugely knowledgeable on joins.... But no, that still gives same results :/ T'is frustrating... Quote Link to comment Share on other sites More sharing options...
jxrd Posted June 13, 2009 Author Share Posted June 13, 2009 If I remove "COUNT(C.`ID`) AS `CommentCount`" it works fine, but obviously doesn't return the number of comments. It's weird...I don't understand why it's not working properly.... Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 13, 2009 Share Posted June 13, 2009 Shouldn't you be using a plain LEFT JOIN, rather than an OUTER? I believe the word OUTER is optional. So LEFT JOIN === LEFT OUTER JOIN. Quote Link to comment Share on other sites More sharing options...
jxrd Posted June 13, 2009 Author Share Posted June 13, 2009 Oh right... Got any ideas why it's not working? Quote Link to comment Share on other sites More sharing options...
jxrd Posted June 13, 2009 Author Share Posted June 13, 2009 Omg I'm such a f*cking retard; I forgot to group by `ID`. Ffs I feel like punching myself sometimes. Quote Link to comment Share on other sites More sharing options...
Maq Posted June 14, 2009 Share Posted June 14, 2009 Omg I'm such a f*cking retard; I forgot to group by `ID`. Ffs I feel like punching myself sometimes. It's always the small things. Shouldn't you be using a plain LEFT JOIN, rather than an OUTER? I believe the word OUTER is optional. So LEFT JOIN === LEFT OUTER JOIN. that's good to know, I was never much of a MySQL guru. I usually go with the trusty LEFT JOIN, but I really should do some more research and tests with the various joins. Quote Link to comment Share on other sites More sharing options...
jxrd Posted June 14, 2009 Author Share Posted June 14, 2009 Aha, you may be in for some optimisation there. Inner joins are faster...but don't return anything if there's no result. So I obviously need a lefty for this, as an entry can have no comments. But yeah, at least something came out of this thread Thanks guys. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.