title: SQL Server null table join
body: <p>I have two tables one of it is LEAGUE, another is MATCH , in MATCH table there is a column as refLeague  now I want to get total matches of week </p>
  
  <p>For example</p>
  
  <pre><code>Id TotalMatches
  -- ------------
  1   12
  2    0
  3    6
  </code></pre>
  
  <p>If there is no match, I want to write 0 as table</p>
  
  <pre><code> SELECT l.Id ,COUNT(m.Id) as TotalMatches 
   FROM LEAGUE l
   LEFT JOIN MATCH m ON l.Id = m.refLeauge
   WHERE 
       m.MatchDate &gt;= DATEADD(dd, DATEDIFF(dd, 1, GETDATE()) / 7 * 7 + 1, 0) 
       AND m.MatchDate &lt; DATEADD(dd, DATEDIFF(dd, -6, GETDATE())/7 * 7 + 1, 0) 
       AND l.refSport = 1
   GROUP BY l.Id
  </code></pre>
  
  <p>I wrote this query but it is not giving any result due to no rows in Match table, but it must be written 0 </p>
  
  <p>Example</p>
  
  <pre><code>Id TotalMatches
  -- ------------
   1      0
   2      0
   3      0
  </code></pre>
  
  <p>Where is my mistake?</p>