Microsoft SQL Server forum - Need help returning a null value, please

Author

Need help returning a null value, please

manning_news@hotmail.com

2005-05-31, 8:24 pm

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

2005-05-31, 8:24 pm

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

2005-05-31, 8:24 pm

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@hotmail.com

2005-05-31, 8:24 pm

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

Hugo Kornelis wrote:
> On 31 May 2005 12:15:27 -0700, Stu wrote:
>
>
> 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

2005-05-31, 8:24 pm

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

Sponsored Links