Can't figure this out...

[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, ');
        
return $this->db->order_by('comments DESC')->limit(5)->get('article')->result_array();

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!