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

To expand on Quassnoi's good answer, for the outer join to work you could change:

AND l.user_id = a.username

To:

AND (l.user_id is null OR l.user_id = a.username)

Answer 3

The SQL Server 2005 RIGHT OUTER JOIN certainly works! :)

What about the following (I've made some assumptions)?:

remove

AND l.user_id = a.username 

and change the joins to

dbo.courses c
LEFT OUTER JOIN lsn_logs l
ON c.course_id = l.course_id
LEFT OUTER JOIN accounts a
ON l.user_id = a.username 

and add some null checks for lsn_logs and accounts fields, such as:

(l.object_id IS NULL OR l.object_id = 'LOGIN')

Answer 4

This ended up working

The null checks that Joe R suggested took care of the problem that Quassnoi mentioned

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 IS NULL OR 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 OR l.user_id IS NULL)
            AND ((a.first_name+' '+a.last_name) = c.instructor)
            AND a.logins > 0
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

Answer 5

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...