LEFT JOIN not returning null when no row exists-CodePudding

I'm working with 2 tables: 1 that deals with basic demographics for a group of people, and 1 that deals with activities that have been completed. What I need to do is pull all results (all people) from the demographics table, and then display activity info for anyone who has completed a certain type of activity within a given timeframe (it's also possible that a given person may have completed multiple activities in that timeframe). If a person has not completed any of the specified activities, I still want them to be visible in the list. I'd like the results to look like this:

PersonID  Name           DateOfBirth   ActivityDate   ActivityType
---------------------------------------------------------------------
1001      John Smith     01/01/1990    10/18/2022     Painting
1002      Jane Doe       12/31/1980    NULL           NULL
1003      Bob Brown      07/04/1995    10/17/2022     Reading
1003      Bob Brown      07/04/1995    09/09/2022     Painting
1004      Mike Jones     03/24/1984    NULL           NULL
1005      Ann Green      11/30/1988    08/29/2022     Writing
1006      Sally Black    05/15/1999    NULL           NULL

It seems like it should be really simple query with a LEFT JOIN between the two tables:

SELECT DISTINCT
    d.PersonID,
    d.Name
    d.DateOfBirth,
    a.ActivityDate
    a.ActivityType
FROM Demographics d
LEFT JOIN Activity a ON d.PersonID = a.PersonID
WHERE ActivityDate BETWEEN DATEADD(yy,-1,GETDATE()) AND GETDATE()
    AND ActivityType IN ('Painting','Reading','Writing')
ORDER BY d.PersonID, a.ActivityDate DESC

However, when I run this query, I'm only getting results for people who have actually completed activities (i.e. the people with NULL results in my example are missing).

PersonID  Name           DateOfBirth   ActivityDate   ActivityType
---------------------------------------------------------------------
1001      John Smith     01/01/1990    10/18/2022     Painting
1003      Bob Brown      07/04/1995    10/17/2022     Reading
1003      Bob Brown      07/04/1995    09/09/2022     Painting
1005      Ann Green      11/30/1988    08/29/2022     Writing

Again, I'd like to display all people from the demographics table, but then show the specified activities for those who have completed them.

Is something wrong with my join? Is the LEFT JOIN the correct way to go about this?

CodePudding user response：

Your WHERE clause is based entirely on your left joined (Activity) table, and you don't allow NULL values anywhere in your WHERE clause. So the join is working, but you are filtering any rows out without an activity.

To correct this, change

WHERE ActivityDate BETWEEN DATEADD(yy,-1,GETDATE()) AND GETDATE()
    AND ActivityType IN ('Painting','Reading','Writing')

WHERE a.PersonId IS NULL 
OR (
    ActivityDate BETWEEN DATEADD(yy,-1,GETDATE()) AND GETDATE()
    AND ActivityType IN ('Painting','Reading','Writing')
)

CodePudding user response：

When you add the condition

ActivityType IN ('Painting','Reading','Writing'),

you actually forbid ActivityType of NULL which would be necessary to include people which have not finished any activity. Same for the condition on ActivityDate

Change your query as follows to also allow NULL values

SELECT DISTINCT
    d.PersonID,
    d.Name
    d.DateOfBirth,
    a.ActivityDate
    a.ActivityType

FROM Demographics d LEFT JOIN Activity a ON d.PersonID = a.PersonID
WHERE (ActivityDate IS NULL OR ActivityDate BETWEEN DATEADD(yy,-1,GETDATE()) AND GETDATE())
AND (ActivityType IS NULL OR ActivityType IN ('Painting','Reading','Writing'))
ORDER BY d.PersonID, a.ActivityDate DESC