Sql Server Null Table Join

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 For example Id TotalMatches -- ---

Solution 1:

Move the right table filters to ON condition

Non matching records will have NULL values in m.MatchDate which will be filtered by the condition in Where clause . Implicitly it will be converted to INNER JOIN. So the condition should be moved to ON clause which tells what are the records to be joined with LEAGUE instead of filtering the result

SELECT l.id, 
       Count(m.id) AS TotalMatches 
FROM   league l 
       LEFTJOINmatch m 
              ON l.id = m.refleauge 
                 AND m.matchdate >= Dateadd(dd, Datediff(dd, 1, Getdate()) /7*7+1, 0) 
                 AND m.matchdate < Dateadd(dd, Datediff(dd, -6, Getdate()) /7*7+1, 0) 
WHERE  l.refsport =1GROUPBY l.id 

Solution 2:

The where is breaking the left join

SELECT l.Id, COUNT(m.Id) as TotalMatches 
 FROM LEAGUE l
 LEFTJOINMATCH m 
   ON l.Id = m.refLeauge
  and m.MatchDate >= dateadd(dd, datediff(dd, 1, getdate()) /7*7+1,0) 
  AND m.MatchDate <  dateadd(dd, datediff(dd,-6, getdate()) /7*7+1,0) 
 where l.refSport=1GROUPBY l.Id

/ 7 * 7 = 1

When I started this answer the other answer was not yet posted