CODEWITHSUNDEEP
phpmysql
Frank Nwoko
Frank Nwoko

Reputation: 3934

Count number of comments in articles

Please I want to add another table (Comments) to this query to count the number of comments for each article.

$query  = "SELECT M.id,M.j_surname,A.j_user_id,A.id,A.jart_title, A.jart_description, A.jart_createddate, COUNT(C.artid) AS count_comments FROM jt_articles A, jt_members M, jt_article_comments C where M.id = A.j_user_id AND C.artid = A.id ORDER BY A.jart_createddate DESC

Upvotes: 0

Views: 204

Answers (1)

Haim Evgi
Haim Evgi

Reputation: 125456

update: something like that (you dont post the scheme of the comments table)

      SELECT M.id,M.j_surname,A.j_user_id,A.id,A.jart_title, A.jart_description,
     A.jart_createddate , COUNT(A.id) AS count_comments
        FROM jt_articles A
left join  jt_members M on M.id = A.j_user_id 
left join  jt_article_comments C on C.artid = A.id        
        group by A.id
        ORDER BY A.jart_createddate DESC

Upvotes: 1

Related Questions