SQL Server 2005 RIGHT OUTER JOIN not working
Posted
by CheeseConQueso
on Stack Overflow
See other posts from Stack Overflow
or by CheeseConQueso
Published on 2010-05-20T13:54:32Z
Indexed on
2010/05/20
14:10 UTC
Read the original article
Hit count: 353
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.
© Stack Overflow or respective owner