title: join with date dimension but don't want NULL for the dates with values
body: <p>I have a query:</p>
  
  <pre><code> SELECT 
      d.FiscalMonth,
      d.FiscalMonthOfYear,
      p.Name
   FROM
       DimDate d
       LEFT JOIN FactSales f on f.SaleDate=d.PKDate
       LEFT JOIN DimPerson p on p.PersonId=f.PersonId
   WHERE d.FiscalYear='2014/7/1'
   group by d.FiscalMonth, d.FiscalMonthOfYear, p.Name
   ORDER BY d.FiscalMonthOfYear asc, p.PersonID asc
  </code></pre>
  
  <p>Which gives me these results:</p>
  
  <p><img src="https://i.stack.imgur.com/Npk6q.png" alt="enter image description here"></p>
  
  <p>Which is all fine, I want to include all months, even the ones that don't have data. (In this case FiscalMonth 2-12.)</p>
  
  <p>The problem I have is with that one NULL value where I have data, IE. FiscalMonthOfYear 1.  The red box. </p>
  
  <p>How would I go about not returning that one "NULL" for the FiscalMonth=2014-07-01?  I've tried some various where clauses but any time I remove the "NULL" values from the results, I also remove all the ones I want (IE. FiscalMonthOfYear 2-12)</p>
  
  <p>Any help or guidance is greatly appreciated!</p>
  
  <p>Thanks!
  -Russ</p>
  
  <p><strong>Update</strong>: </p>
  
  <p>DimDate table has primary key PKDate, which is one row for every date:</p>
  
  <pre><code>  DimDate
    PKDate ....
    2014-07-01
    2014-07-02
    2014-07-03
    etc.
  </code></pre>
  
  <p>FaceSales table has one ore many Sales transactions for a given day:</p>
  
  <pre><code>  FactSales
    SaleDate       Amount
    2014-07-01     34.99
    2014-07-01     21.89
    2014-07-02     24.77
    2014-07-04     22.77
  </code></pre>
  
  <p>The problem is that FactSales may not have a sale on a particular day.  So my query is finding that one (or many) days with no transactions, and because of the LEFT JOIN is returning it.  How would I go about removing this result so it's not in my results?</p>
  
  <pre><code>  SELECT
         d.PKDate
         ,f.SaleDate
  
   FROM
       DimDate d
       LEFT JOIN FactSales f on f.SaleDate=d.PKDate
       LEFT JOIN DimPerson p on p.PersonId=f.PersonId
   WHERE d.FiscalYear='2014/7/1'
   ORDER BY d.PKDate
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/EtHmT.png" alt="enter image description here"></p>