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.
