Getting dates for each user - even if no activity in between

Question

I have a problem, I feel like I know this but I cannot find a solution.

I have a temp table where I generated a date range.

TempTable

Date
11/1/2017
11/2/2017
11/3/2017
11/4/2017
11/5/2017

Need to join to this table:

Date        User    Permissions
11/1/2017   A       2
11/2/2017   A       4   
11/5/2017   A       7
11/3/2017   B       1
11/4/2017   B       2

This is the result I am looking for. However, a join does not yield result.

Date        User    Permissions
11/1/2017   A       2
11/2/2017   A       4
11/3/2017   A       0
11/4/2017   A       0
11/5/2017   A       5
11/1/2017   B       0
11/2/2017   B       0
11/3/2017   B       1
11/4/2017   B       2
11/5/2017   B       0

Answer 1

Cross join dates with users and left join the other table on to that.

select t.date,u.user,coalesce(o.permissions,0) as permissions
from temptable t
cross join (select distinct user from other_tbl) u
left join other_tbl o on o.user=u.user and o.date=t.date