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. Link to comment https://forums.phpfreaks.com/topic/162064-solved-odd-results/ 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? Link to comment https://forums.phpfreaks.com/topic/162064-solved-odd-results/#findComment-855140 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... Link to comment https://forums.phpfreaks.com/topic/162064-solved-odd-results/#findComment-855141 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.... Link to comment https://forums.phpfreaks.com/topic/162064-solved-odd-results/#findComment-855152 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. Link to comment https://forums.phpfreaks.com/topic/162064-solved-odd-results/#findComment-855192 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? Link to comment https://forums.phpfreaks.com/topic/162064-solved-odd-results/#findComment-855198 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. Link to comment https://forums.phpfreaks.com/topic/162064-solved-odd-results/#findComment-855209 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. Link to comment https://forums.phpfreaks.com/topic/162064-solved-odd-results/#findComment-855357 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. Link to comment https://forums.phpfreaks.com/topic/162064-solved-odd-results/#findComment-855369 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.