0

Hello

I want a query to get my result

I have two tables:
1) articles
2) comments


1) This is my articles table:

--------------------------------------
id  |  article_title  | article_body |
--------------------------------------
1   |  dummy article  | some body    |
2   |  article2       | body2        |
3   |  article3       | body33       |
4   |  article4       | body444      |
--------------------------------------

2) This is my comments table:

----------------------------------------------
id  |  comments_title  | article_id | status |
----------------------------------------------
1   |  good article  | 1            | 1      |
2   |  best article  | 1            | 1      |
3   |  comment       | 2            | 1      |
4   |  comment22     | 4            | 0      |
----------------------------------------------

Currently I am fetching only article list by this query:

SELECT * FROM articles

Now I also want to display total comments for each article and also total active comments for each article. Example:

--------------------------------------------------------------------------
article_title  |  article_body  | total comments | total active comments |
--------------------------------------------------------------------------

Active comments are with status=1 in comments table.

I know I need to use JOIN but I am not clear how to use this on this type of query.

Thanks in advance.

2
Contributors
1
Reply
3
Views
6 Years
Discussion Span
Last Post by rch1231
1

Try this and see how it works:

SELECT article.article_title, 
article.article.body, 
count(*), 
sum(comments.status) 
from articles, comments
where articles.ID = comments.Article_ID
group by article.article_title

Should be about right.

Edited by mike_2000_17: Fixed formatting

Votes + Comments
good simple concise answer.
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.