sql server (alternate): 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.

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

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

[quoted text, click to view]