CODEWITHSUNDEEP
mysql
Joem Maranan
Joem Maranan

Reputation: 150

Selecting data and count from another table at the same time

I just want to echo the articles and Order it by the most number it has been commented by users. This are my two tables

tbl_articles

article_id  |  articles_title  |
 1          |     This is 1st  |
 2          |     This 2nd    |

tbl_comment:

comment_id  |   user_id     | article_id | comment_msg
 1          |       1       |    1       | my comment1
 2          |       1       |    2       | my comment2
 3          |       2       |    1       | some comment1

How to join these tables and force a result like this

 article_id|articles_title|  comment_count  |
     1     |  This is 1st |    2            |
     2     |  This 2nd    |    1            |

Thanks

Upvotes: 1

Views: 1131

Answers (2)

John Woo
John Woo

Reputation: 263723

The query below uses INNER JOIN which will only display all articles on the result if it has atleast 1 comment. If you want to display articles even without comment yet, change INNER JOIN into LEFT JOIN.

SELECT  a.article_ID,
        a.article_title,
        COUNT(*) Comment_Count
FROM    articles a
        INNER JOIN comment b
            ON a.article_ID = b.article_ID
GROUP   BY a.article_ID, a.article_title

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is a simple join query with an aggregation:

select article_id, articles_title, count(c.comment_id) as comment_count
from tbl_articles a left outer join
     tbl_comment c
     on a.article_id = c.article_id
group by article_id, articles_title

This uses a left outer join to keep all the articles, even when they have no comments.

Upvotes: 1

Related Questions