Can't figure this out... - 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: Can't figure this out... (/showthread.php?tid=13596) |
Can't figure this out... - El Forum - 11-28-2008 [eluser]iainco[/eluser] Hey guy, Got a problem with a query. Got 2 MySQL tables setup like this: article (ID, feedID, title, url, email, rating, view) and article_comment (ID, articleID, userID, date, comment) I can order my articles by the number of times it has been emailed, its rating and its view count but I also want to be able to order them by the number of comments associated with said article. Below is my attempt which isn't quite working. Giving me the database error "Subquery returns more than 1 row." There is one comment saved in the article_comment table. Code: $this->db->select('(SELECT count(*) FROM article_comment WHERE article_comment.articleID = (SELECT ID from article)) AS comments, '); I just need it to return the number of comments so I can "order_by(comments DESC)" and also have all the associated article information. Any help would be much appreciated, thanks! Can't figure this out... - El Forum - 11-28-2008 [eluser]GSV Sleeper Service[/eluser] you'll have to join the two tables. something like this should work (no promises, this is off the top of my head, plus it's friday night, I'm stoned :/) Code: select a.*,count(c.id) as num_comments |