SQL Server 2005 RIGHT OUTER JOIN not working

Question

I look at access logs for specific courses. I need to show all courses, even if they do not exist in the log table. Therefore, the outer join ... but after trying (presumably) all the changes to LEFT OUTER , RIGHT OUTER , INNER and placing the tables in the SQL code, I could not get my result.

Here is what I run:

 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 this something in the WHERE that prevents me from getting the course_id, which is not in lsn_logs? So am I joining the tables?

In short, I want all of the course_id to be independent of their existence in lsn_logs.

Answer 1

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

Answer 2

Is this something in the WHERE clause that prevents me from getting a course_id that is not in lsn_logs?

Yes it is.

You use equality conditions in your WHERE , which effectively filters the NULL strings 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 3

To extend a good answer to Quassnoi, for an external connection to work, you can change:

 AND l.user_id = a.username 

To:

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

Answer 4

Surely running SQL Server 2005 RIGHT OUTER JOIN! :)

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

remove

 AND l.user_id = a.username 

and change the connections 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 the lsn_logs fields and accounts, for example:

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

Answer 5

It finished the job

The null checks that Joe R suggested took care of the problem that Kvasnoy 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