SQL Server ПРАВИЛЬНОЕ ВНЕШНЕЕ ОБЪЕДИНЕНИЕ 2005 года, не работающее
Я ищу журналы доступа для определенных курсов. Я должен показать все курсы, даже если они не существуют в таблице журналов. Следовательно внешнее объединение...., но после попытки (по-видимому), всех изменений LEFT OUTER, RIGHT OUTER, INNER и размещение таблиц в рамках кода SQL, я не мог получить свой результат.
Вот то, что я выполняю:
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
Это - что-то в WHERE пункт это препятствует тому, чтобы я получил course_id, которые не существуют в lsn_logs? Действительно ли это - способ, которым я присоединяюсь к таблицам?
Снова, короче говоря, я хочу весь course_id независимо от их существования в lsn_logs.
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...
Что-то в предложении WHERE мешает мне получить идентификаторы курса, которых не существует в lsn_logs?
Да, это так.
Вы используете условия равенства в предложении WHERE, которые эффективно отфильтровывают NULL строки, созданные OUTER JOIN.
Обновление:
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
Чтобы расширить хороший ответ Квассного, для работы внешнего соединения вы можете изменить:
AND l.user_id = a.username
Кому:
AND (l.user_id is null OR l.user_id = a.username)
В SQL Server 2005 RIGHT OUTER JOIN определенно работает! :)
Что насчет следующего (я сделал некоторые предположения)?:
удалить
AND l.user_id = a.username
и изменить джоины на
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
и добавить некоторые проверки нуля для полей lsn_logs и accounts, например:
(l.object_id IS NULL OR l.object_id = 'LOGIN')
В итоге это сработало
Нулевые проверки, предложенные Джо Р., решили проблему, о которой упоминал Квассной
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