Joining Table A and B to get elements of both
<p>I have two tables:</p>

<p>Table 'bookings': </p>

<pre><code>  id    |    date   | hours
--------------------------
   1    | 06/01/2016 |    2
   1    | 06/02/2016 |    1
   2    | 06/03/2016 |    2
   3    | 06/03/2016 |    4
</code></pre>

<p>Table 'lookupCalendar':</p>

<pre><code>date 
-----
06/01/2016
06/02/2016
06/03/2016
</code></pre>

<p>I want to join them together so that I have a date for each booking so that the results look like this:</p>

<p>Table 'results':</p>

<pre><code>  id    |    date   | hours
--------------------------
   1    | 06/01/2016 |    2
   1    | 06/02/2016 |    1
   1    | 06/03/2016 |    0  &lt;-- Added by query
   2    | 06/01/2016 |    0  &lt;-- Added by query
   2    | 06/02/2016 |    0  &lt;-- Added by query
   2    | 06/03/2016 |    2
   3    | 06/01/2016 |    0  &lt;-- Added by query
   3    | 06/02/2016 |    0  &lt;-- Added by query
   3    | 06/03/2016 |    4
</code></pre>

<p>I have tried doing a cross-apply, but that doesn't get me there, neither does a full join. The <code>FULL JOIN</code> just gives me nulls in the id column and the cross-apply gives me too much data. </p>

<p>Is there a query that can give me the <code>results</code> table above?</p>

<p><strong>More Information</strong></p>

<p>It might be beneficial to note that I am doing this so that I can calculate an average hours booked over a <em>period of time</em>, not just the number of records in the table. </p>

<p>Ideally, I'd be able to do </p>

<pre><code>SELECT AVG(hours) AS my_average, id
FROM bookings
GROUP BY id
</code></pre>

<p>But since that would just give me a count of the records instead of the count of the days I want to cross apply it with the dates. Then I think I can just do the query above with the <code>results</code> table.</p>