CODEWITHSUNDEEP
sqlsql-server
whobutsb
whobutsb

Reputation: 1075

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

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!

Upvotes: 3

Views: 2460

Answers (3)

devio
devio

Reputation: 37215

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')

Upvotes: 2

KM.
KM.

Reputation: 103587

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

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 838266

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)

Upvotes: 2

Related Questions