Need help returning a null value, please

[manning_news]

I've got the following query in SQL 2000:

select a.SSN, a.MonthName, a.IMClinicDay,
b.IMClinicDay as SecondDay
from tblResidentRotations a
inner join view7 b
on a.SSN = b.SSN
where a.AcademicYear = '2004-2005' and a.SSN = '999999999' and
datename(month, a.IMClinicDateFirst) = b.MonthName

This query returns a resultset like this:

<SSN> <Month> <a.IMClinicDay> <SecondDay>
999999999 July Friday PM Tuesday PM
999999999 September Tuesday PM Friday PM
999999999 October None Friday PM
999999999 November Friday PM Tuesday PM
999999999 January Tuesday PM Friday PM
999999999 April Friday PM Monday PM
....and so on
For some of the months, there is a null value for "b.IMClinicDay". For
example, it's null for August, December, and February. I want my
resultset to look like this:

<SSN> <Month> <a.IMClinicDay> <SecondDay>
999999999 July Friday PM Tuesday PM
999999999 August Tuesday PM null
999999999 September Tuesday PM Friday PM
999999999 October None Friday PM
999999999 November Friday PM Tuesday PM
999999999 December Tuesday PM null
999999999 January Tuesday PM Friday PM
999999999 February Friday PM null
999999999 April Friday PM Monday PM
....and so on
How can I return a null for these days? Thanks for any help or
advice.

[Stu]

Use a LEFT JOIN instead of an INNER JOIN.

SELECT a.SSN, a.MonthName, a.IMClinicDay,
b.IMClinicDay as SecondDay
FROM tblResidentRotations a LEFT JOIN view7 b ON a.SSN = b.SSN
WHERE a.AcademicYear = '2004-2005'
AND a.SSN = '999999999'
AND datename(month, a.IMClinicDateFirst) = b.MonthName

HTH
Stu

[Hugo Kornelis]

On 31 May 2005 12:15:27 -0700, Stu wrote:

Use a LEFT JOIN instead of an INNER JOIN.

SELECT a.SSN, a.MonthName, a.IMClinicDay,
b.IMClinicDay as SecondDay
FROM tblResidentRotations a LEFT JOIN view7 b ON a.SSN = b.SSN
WHERE a.AcademicYear = '2004-2005'
AND a.SSN = '999999999'
AND datename(month, a.IMClinicDateFirst) = b.MonthName

HTH
Stu

Hi Stu,

One correction. The last line of the WHERE clause has to move to the ON
clause:

SELECT a.SSN, a.MonthName, a.IMClinicDay,
b.IMClinicDay as SecondDay
FROM tblResidentRotations a
LEFT JOIN view7 b
ON a.SSN = b.SSN
AND datename(month, a.IMClinicDateFirst) = b.MonthName
WHERE a.AcademicYear = '2004-2005'
AND a.SSN = '999999999'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[manning_news]

Thanks Stu and Hugo. I got the resultset I wanted.

Hugo Kornelis wrote:

On 31 May 2005 12:15:27 -0700, Stu wrote:

Use a LEFT JOIN instead of an INNER JOIN.

SELECT a.SSN, a.MonthName, a.IMClinicDay,
b.IMClinicDay as SecondDay
FROM tblResidentRotations a LEFT JOIN view7 b ON a.SSN = b.SSN
WHERE a.AcademicYear = '2004-2005'
AND a.SSN = '999999999'
AND datename(month, a.IMClinicDateFirst) = b.MonthName

HTH
Stu

Hi Stu,

One correction. The last line of the WHERE clause has to move to the ON
clause:

SELECT a.SSN, a.MonthName, a.IMClinicDay,
b.IMClinicDay as SecondDay
FROM tblResidentRotations a
LEFT JOIN view7 b
ON a.SSN = b.SSN
AND datename(month, a.IMClinicDateFirst) = b.MonthName
WHERE a.AcademicYear = '2004-2005'
AND a.SSN = '999999999'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Stu]

Ahhh, I suck at multitasking. Shows what happens when you cut and
paste without looking too closely :)