Help with Conditional JOIN query 2

[kevinforbes]

Hi all, I have 2 tables that I want to join based on the following conditions. I always want to display everything from the "courses" table but I only want to join data from the "sessions" table if the session_user_id matches an ASP variable and the "course_code" field in both tables match each other.

The query I came up with looks as follows but doesn't work:

SELECT
courses.course_code, courses.course_name, sessions.core__lesson_status, sessions.core__score
FROM courses
LEFT OUTER JOIN sessions ON sessions.course_code = courses.course_code
WHERE sessions.user_sap_id =10 <--- asp var

thanks for your help,
K

 

[SonOfEmidec1100]

How does this work for you

SELECT courses.course_code, courses.course_name, sessions.core__lesson_status, sessions.core__score
FROM courses
INNER JOIN sessions ON sessions.course_code = courses.course_code
WHERE sessions.user_sap_id =10

UNION ALL

SELECT courses.course_code, courses.course_name, Null AS sessions.core__lesson_status, Null AS sessions.core__score
FROM courses
LEFT OUTER JOIN sessions ON sessions.course_code = courses.course_code
WHERE sessions.core__lesson_status IS NULL Or sessions.core__lesson_status <>10

ORDER BY courses.course_code, courses.course_name

 

[SemperFiDownUnda]

ok problem is if you do a OUTER JOIN on a table then use any of the column in that table in the WHERE you actually perform a inner join.

You can do what you want in a few ways

SELECT c.course_code, c.course_name,
s.core__lesson_status, s.core__score
FROM courses c
LEFT OUTER JOIN sessions s
ON s.course_code = c.course_code
AND s.user_sap_id = 10

Here I brought the condition into the join.

or you could

SELECT c.course_code, c.course_name,
s.core__lesson_status, s.core__score
FROM courses c
LEFT OUTER JOIN sessions s
ON s.course_code = c.course_code
WHERE (s.user_sap_id = 10 OR s.user_sap_id IS NULL)

now these 2 will almost work alike. The only difference is that if your s.user_sap_id allows nulls and there is data that has nulls in it then you'll probably get records you don't want. Best to go with the first method its cleaner

 

[kevinforbes]

ah, I see...I was missing the all important &quot;AND&quot; thank you very much!

Kev