Join calendar table with data table and fill in blank data

You can cross join the calendar with the list of distinct topics available in the table, and then bring the actual dataset with a left join:

select c.date, x.topicname, t.cnt
from calendar c
cross join (select distinct topicname from mytable) x
left join mytable t 
    on t.date = c.date and t.topicname = x.topicname

Note: count is a language keyword, I used cnt instead.

Ideally, you would have a separate table to store the topics, that you could use instead of the select distinct subquery.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top