SQL Server 2005 RIGHT OUTER JOIN not working

Question

I'm looking up access logs for specific courses. I need to show all the courses even if they don't exist in the logs table. Hence the outer join.... but after trying (presumably) all of the variations of LEFT OUTER, RIGHT OUTER, INNER and placement of the tables within the SQL code, I couldn't get my result.

Here's what I am running:

SELECT   (a.first_name+' '+a.last_name) instructor,
            c.course_id,
            COUNT(l.access_date) course_logins,
            a.logins system_logins,
            MAX(l.access_date) last_course_login,
            a.last_login last_system_login
FROM       lsn_logs l RIGHT OUTER JOIN courses c ON l.course_id = c.course_id,
            accounts a
WHERE     l.object_id = 'LOGIN' 
            AND c.course_type = 'COURSE' 
            AND c.course_id NOT LIKE '%TEST%' 
            AND a.account_rights > 2
            AND l.user_id = a.username
            AND ((a.first_name+' '+a.last_name) = c.instructor)
GROUP BY    c.course_id, 
            a.first_name, 
            a.last_name,
            a.last_login,
            a.logins,
            c.instructor
ORDER BY    a.last_name,
            a.first_name,
            c.course_id,
            course_logins DESC

Is it something in the WHERE clause that's preventing me from getting course_id's that don't exist in lsn_logs? Is it the way I'm joining the tables?

Again, in short, I want all course_id's regardless of their existence in lsn_logs.

Answer 1

Is it something in the WHERE clause that's preventing me from getting course_id's that don't exist in lsn_logs?

Yes, it is.

You use equality conditions in your WHERE clause that effectively filter out the NULL rows generated by the OUTER JOIN.

Update:

SELECT  c.instructor,
        c.course_id,
        l.course_logins,
        a.logins system_logins,
        l.last_course_login,
        a.last_login last_system_login
FROM    courses с
JOIN    accounts a
ON      a.first_name + ' ' + a.last_name = c.instructor
CROSS APPLY
        (
        SELECT  COALESCE(COUNT(access_date), 0) course_logins,
                MAX(access_date) last_course_login
        FROM    lsn_logs l
        WHERE   l.object_id = 'LOGIN'
                AND l.course_id = c.course_id
                AND l.user_id = a.username
        ) l
WHERE   c.course_type = 'COURSE' 
        AND c.course_id NOT LIKE '%TEST%' 
        AND a.account_rights > 2
ORDER BY
        a.last_name,
        a.first_name,
        c.course_id,
        course_logins DESC

Answer 2

SELECT...    
FROM courses c
     INNER JOIN accounts a
        ON (a.first_name+' '+a.last_name) = c.instructor
     LEFT OUTER JOIN lsn_logs l
        ON l.course_id = c.course_id
        AND l.user_id = a.username
        AND l.object_id = 'LOGIN' 
WHERE c.course_type = 'COURSE'  
    AND c.course_id NOT LIKE '%TEST%'
    AND a.account_rights > 2 
    AND a.logins > 0 
GROUP BY...
ORDER BY...