SQL join with count

Question

I have 2 tables; articles and article_shares (how many times an article has been shared)

I want to show all articles by a user (id 63) and how many times they have shared the article (this could be 0)

Articles:

article_id - user_id  - title - date

Article_shares

article_id

I'm trying the following but it only returning the one row where there are shares but I want to show all, even if the number of shares is 0 (there are 7 articles in this case)

SELECT *, DATE_FORMAT(a.date, '%d/%m/%y') as article_date, count(*) 
from articles a 
join article_shares ash on ash.article_id = a.article_id
where (a.user_id = '63') order by a.article_title asc

Answer 1

Change your join to a left join

Something like

SELECT *, DATE_FORMAT(a.date, '%d/%m/%y') as article_date, count(*)  
from articles a  
left join article_shares ash on ash.article_id = a.article_id 
where (a.user_id = '63') order by a.article_title asc

Have a look at this example

SQL Fiddle DEMO

Also maybe have a look at Introduction to JOINs – Basic of JOINs

Answer 2

Just do LEFT JOIN instead of JOIN. It will create NULL entries for articles with no shares.