Returning NULLs in SQL if joined table is missing records for that Date

Question

Good Morning All. I've been struggling with this issue for a while now, and I can't seem to wrap my head around it.

So I have two tables in my Database

tblDateTrans

CREATE TABLE [dbo].[tblDateTrans](
    [Date] [smalldatetime] NOT NULL,
) 

This table is an external calendar table that contains all the dates from 1/1/2007 - 1/1/2011, it also contains addtional info like holiday info, company period, etc. But that isn't important for this question.

My second table is

    tblSurveySession
    CREATE TABLE [dbo].[tblSurveySession](
        [surveySessionID] [int] IDENTITY(1,1) NOT NULL,
        [guestID] [int] NULL,
        [surveyID] [int] NOT NULL,
        [FK_StoreId] [int] NULL,
        [surveyCompletedDate] [datetime] NULL
)

This table contains a list of surveys sent out and completed by our guests over a period of time. FK_StoreId is the business unit ID for the company, and surveyCompletedDate only contains dates and no times.

My goal is to return a list of surveySessionIDs and Dates using a LEFT OUTER JOIN on the tblDateTrans table, I would like to return all the dates regardless if there are values. So I tried to run this query:

SELECT     tblDateTrans.Date, dbo.tblSurveySession.surveySessionID, dbo.tblSurveySession.FK_StoreId
FROM         OPENQUERY([APOLLO], 'select Date FROM apollo.nhcglobal.dbo.tblDateTrans') AS tblDateTrans LEFT OUTER JOIN
                      dbo.tblSurveySession ON tblDateTrans.Date = dbo.tblSurveySession.surveyCompletedDate
WHERE     (tblDateTrans.Date >= '1/1/2010') AND (tblDateTrans.Date <= '2/1/2010') AND (dbo.tblSurveySession.FK_StoreId = 4)

My returned data looks like so:

Date                    surveySessionID     FK_StoreId
2010-01-01 00:00:00.000     12702           4
2010-01-01 00:00:00.000     12736           4
2010-01-01 00:00:00.000     12456           4
2010-01-03 00:00:00.000     12662           4
2010-01-04 00:00:00.000     12660           4
2010-01-05 00:00:00.000     12510           4
2010-01-05 00:00:00.000     12889           4
2010-01-24 00:00:00.000     13751           4
2010-01-25 00:00:00.000     13793           4
2010-01-28 00:00:00.000     13958           4
2010-01-30 00:00:00.000     14059           4
2010-01-31 00:00:00.000     14139           4

My Goal is to have the query return the data like so:

Date                    surveySessionID     FK_StoreId
2010-01-01 00:00:00.000     12702           4
2010-01-01 00:00:00.000     12736           4
2010-01-01 00:00:00.000     12456           4
2010-01-02 00:00:00.000     NULL            NULL
2010-01-03 00:00:00.000     12662           4
2010-01-04 00:00:00.000     12660           4
2010-01-05 00:00:00.000     12510           4
2010-01-05 00:00:00.000     12889           4
2010-01-06 00:00:00.000     NULL            NULL
2010-01-07 00:00:00.000     NULL            NULL
2010-01-08 00:00:00.000     NULL            NULL
2010-01-09 00:00:00.000     NULL            NULL
2010-01-10 00:00:00.000     NULL            NULL
2010-01-11 00:00:00.000     NULL            NULL
2010-01-12 00:00:00.000     NULL            NULL
2010-01-13 00:00:00.000     NULL            NULL
2010-01-14 00:00:00.000     NULL            NULL
2010-01-15 00:00:00.000     NULL            NULL
2010-01-16 00:00:00.000     NULL            NULL
2010-01-17 00:00:00.000     NULL            NULL
2010-01-18 00:00:00.000     NULL            NULL
2010-01-19 00:00:00.000     NULL            NULL
2010-01-20 00:00:00.000     NULL            NULL
2010-01-21 00:00:00.000     NULL            NULL
2010-01-22 00:00:00.000     NULL            NULL
2010-01-23 00:00:00.000     NULL            NULL
2010-01-24 00:00:00.000     13751           4
2010-01-25 00:00:00.000     13793           4
2010-01-28 00:00:00.000     13958           4
2010-01-30 00:00:00.000     14059           4
2010-01-31 00:00:00.000     14139           4

I figured an LEFT OUTER JOIN would force the query to look at all the dates and return NULLS on days that are missing surveySessionIDs and FK_StoreIds. We have run into this sort of issue before with other projects so solving it would be a huge help for us in the future. Thank you all for the help!

Answer 1

You're filtering away some of the rows you want because of your WHERE clause. Try changing the last part of your WHERE clause to also allow NULLs, i.e from this:

AND (dbo.tblSurveySession.FK_StoreId = 4)

to this:

AND (dbo.tblSurveySession.FK_StoreId = 4 OR
     dbo.tblSurveySession.FK_StoreId IS NULL)

Answer 2

move the (dbo.tblSurveySession.FK_StoreId = 4) from the WHERE to the LEFT JOIN's ON clause, like:

LEFT OUTER JOIN dbo.tblSurveySession ON tblDateTrans.Date = dbo.tblSurveySession.surveyCompletedDate AND dbo.tblSurveySession.FK_StoreId = 4

Answer 3

invert the join direction

SELECT tblDateTrans.Date, dbo.tblSurveySession.surveySessionID, dbo.tblSurveySession.FK_StoreId
FROM   OPENQUERY([APOLLO], 'select Date FROM apollo.nhcglobal.dbo.tblDateTrans') AS tblDateTrans 
LEFT OUTER JOIN dbo.tblSurveySession 
    ON tblDateTrans.Date = dbo.tblSurveySession.surveyCompletedDate
    AND (dbo.tblSurveySession.FK_StoreId = 4)
WHERE (tblDateTrans.Date >= '1/1/2010') AND (tblDateTrans.Date <= '2/1/2010')