Select Count Outer Joins and WHERE

Question

I have the following SQL:

SELECT 
    ud.firstname + ' ' + ud.lastname, 
    COUNT(a.createdDate) AS ActivityCount, 
    au.LastActivityDate
FROM 
    dbo.aspnet_Users au 
    INNER JOIN dbo.UserDetails ud 
    ON au.UserId = ud.AspNetUserId
    LEFT OUTER JOIN dbo.audit a 
    ON au.LoweredUserName = a.username
WHERE 
    (
        au.LoweredUserName IN ('u1', 'u2', 'u3')
    ) 
AND a.createdDate > '2012-09-18'
GROUP BY ud.firstname + ' ' + ud.lastname, au.LastActivityDate
ORDER BY au.LastActivityDate DESC

But let's say that u1 and u3 doesn't have any activity since the 2012-09-18 -- then the result only contains u2.

I thought the LEFT OUTER JOIN made sure that I get all records from aspnet_Users -- but I guess this is due to the seconds condition in my WHERE clause filtering another table...

What do I need to change in order to get all the specified users, and then a zero as ActivityCount if no data exists for the given period?

Answer 1

SELECT 
    ud.firstname + ' ' + ud.lastname,
    sum(case when a.createdDate is null then 0 else 1 end) AS ActivityCount, 
    au.LastActivityDate
FROM 
    dbo.aspnet_Users au 
    INNER JOIN dbo.UserDetails ud 
    ON au.UserId = ud.AspNetUserId
    LEFT OUTER JOIN dbo.audit a 
    ON au.LoweredUserName = a.username
WHERE 
    (au.LoweredUserName IN ('u1', 'u2', 'u3')) 
AND (a.createdDate > '2012-09-18' or a.createdDate is null)
GROUP BY 
    ud.firstname + ' ' + ud.lastname,
    au.LastActivityDate
ORDER BY au.LastActivityDate DESC

Answer 2

but I guess this is due to the seconds condition in my WHERE clause filtering another table...

Right, the condition on createdDate in WHERE clause is suppressing the records from dbo.audit. You can move that to the LEFT OUTER JOIN condition list to get the desired results.