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.