CODEWITHSUNDEEP
mysqlsql
StudioTime
StudioTime

Reputation: 23959

SQL join with count

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

Upvotes: 2

Views: 96

Answers (4)

Adriaan Stander
Adriaan Stander

Reputation: 166346

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

Upvotes: 4

Yogendra Singh
Yogendra Singh

Reputation: 34367

Change your join condition as LEFT JOIN and try. This will return the articles share even if the article_id is null in them.

Upvotes: 3

valex
valex

Reputation: 24134

you should use LEFT JOIN

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

Upvotes: 2

sashkello
sashkello

Reputation: 17871

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

Upvotes: 3

Related Questions