Multiple joins and multiple counts - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Multiple joins and multiple counts (/showthread.php?tid=13336) |
Multiple joins and multiple counts - El Forum - 11-19-2008 [eluser]Daniel H[/eluser] I know I should be posting this in an db forum, but I hope this is an easy problem someone can help me with... Essentially I am trying to return the comment count for a given post. I'm doing this using a LEFT join and aggregate COUNT, however because I need to left join the category/tags table to the post, the query I'm using multiplies the number of tags/categories by the number of comments! Here's a simplified query... can anyone suggest how I ought to be doing this? Maybe my comment counts have to be separate queries...? Code: SELECT DISTINCT posts.id, posts.title, Multiple joins and multiple counts - El Forum - 11-19-2008 [eluser]Daniel H[/eluser] ...now I'm using two subqueries to get the counts... not sure if that is good practice?! Code: SELECT posts.id, posts.title, Multiple joins and multiple counts - El Forum - 11-19-2008 [eluser]Phil Sturgeon[/eluser] Your sub query approach will work out better. Before you probably had problems as each time you are joining tables together you are saying "all the fields should be returned together". This means that the count will count empty strings all over the place, and give you identical or screwy results. A sub query for each count is usual best practice. Multiple joins and multiple counts - El Forum - 11-19-2008 [eluser]Daniel H[/eluser] Okay great - thanks Phil, Dan. |