Show all rows with 0 if no records found on second table

Question

I need to see all the records from table A, if no matching record found on table B then show 0 with corresponding value of A.

SELECT r.date
     , l.total_user
  FROM daterange r
  LEFT 
  JOIN logs l
    ON r.date = l.created_date
 WHERE r.date BETWEEN '2020-09-14' AND '2020-11-14' AND   l.view_id =(SELECT view_id from logs where user_id = 63)

in above query few records doesn't exist in logs table but i need 0 with date as well.

Any idea how to resolve this please

i need all dates from '2020-09-14' to '2020-11-14' from daterange table

score 0 · Answer 1 · answered Nov 15 '20 at 11:56

0

Presumably, you want coalesce():

select d.date, coalesce(l.total_user, 0) as total_user
from daterange d 
left join logs l on d.date = l.created_date
where d.date between '2020-09-14' and '2020-11-14'

When there is no match for a given date in the logs table, this returns 0 for total_user instead of null. Of course, this assumes that column total_user is of a numeric datatype to start with.

Note that table aliases make the query easier to write and read.

answered Nov 15 '20 at 11:56

GMB

216,147
25
84
135

this is missing '2020-09-14' and if i use one more condition like select d.date, coalesce(l.total_user, 0) as total_user from daterange d left join logs l on d.date = l.created_date where d.date between '2020-09-14' and '2020-11-14' AND l.`view_id` =(SELECT view_id from logs where user_id = 63). its giving few records – sunny jindal Nov 15 '20 at 12:33

shehanpathi · Answer 2 · 2020-11-15T13:36:43.847

0

You can use IFNULL() function.

SELECT r.date
     , IFNULL(l.total_user, 0)
  FROM daterange r
  LEFT 
  JOIN logs l
    ON r.date = l.created_date
 WHERE r.date BETWEEN '2020-09-14' AND '2020-11-14' AND   l.view_id =(SELECT 
 view_id from logs where user_id = 63)

edited Nov 15 '20 at 13:36

answered Nov 15 '20 at 12:20

shehanpathi

312
4
15

this is missing '2020-09-14' and if i use one more condition like select d.date, coalesce(l.total_user, 0) as total_user from daterange d left join logs l on d.date = l.created_date where d.date between '2020-09-14' and '2020-11-14' AND l.view_id =(SELECT view_id from logs where user_id = 63). its giving few records – – sunny jindal Nov 15 '20 at 12:34
this query is working fine but not giving me all results. this is only giving me matching records now – sunny jindal Nov 15 '20 at 13:39
Can you edit the question to be more descriptive? You can add expected and actual results. – shehanpathi Nov 15 '20 at 13:43

Show all rows with 0 if no records found on second table

2 Answers2