Want help of JOIN query

[bimaljr]

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.

[rch1231]

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.