How to show results from a join even if where clause removes those results

I have a query that joins to a Counties table. That table contains 14 different counties. I am counting the number of members in each county from a span of time. When that span of time I choose doesn't include any members from a certain county, the county doesn't show up, no matter if I use a Right join or Inner join to the county table. My goal is that if no members are returned f开发者_JAVA百科rom a county, to show 0 for that county total. How would I do this?

SELECT DISTINCT
    e.County,
    COUNT(DISTINCT d.MemberID) AS TotalUniqueProviders
FROM
    dw.FactMedicalClaimLine a
INNER JOIN 
    dw.DimMember d
ON
    a.MemberKey = d.MemberKey
RIGHT JOIN
    dw.DimGeography e
ON
    a.GeographyKey = e.GeographyKey
WHERE
    LEFT(ServiceDate, 6) >= 201001
    AND LEFT(PaidDate, 6) BETWEEN 201010 AND 201012

Move your filter into the JOIN clause

RIGHT JOIN
    dw.DimGeography e
ON
    a.GeographyKey = e.GeographyKey
AND
    LEFT(ServiceDate, 6) >= 201001
    AND LEFT(PaidDate, 6) BETWEEN 201010 AND 201012

The country rows preserved because of the OUTER JOIN will have NULL for ServiceDate and PaidDate and so end up getting excluded by your WHERE clause.

You also seem to be missing a GROUP BY e.GeographyKey in the posted version of your query and you don't need DISTINCT with GROUP BY.

Replace WHERE with AND i.e:

ON     a.GeographyKey = e.GeographyKey 
AND    LEFT(ServiceDate, 6) >= 201001     
AND LEFT(PaidDate, 6) BETWEEN 201010 AND 201012