Right outer join with where clause not returning expected results

Question

I have a SQL statement that works perfectly:

SELECT 
    DailyRequest.*
FROM 
    DailyRequest
RIGHT OUTER JOIN
    Facilities ON FacilityID = Facilities.ID 

This shows all the records in Facilities, as expected. However, I need to show only the records for a specified date, but still all of them from Facilities. I thought the SQL below would work, but it does not.

This SQL only shows the records from DailyRequest:

SELECT 
    DailyRequest.*
FROM
    DailyRequest
RIGHT OUTER JOIN
    Facilities ON FacilityID = Facilities.ID 
WHERE 
    RequestDate = '7/21/2021'

The date seems to introduce the problem, but I'm not sure what I am doing wrong. I want to show all the records from the joined tables, even if they don't exist in DailyRequest.

Answer 1

Pretty sure you need to apply the date limitation in the join itself for it to properly effect results. Try:

SELECT 
DailyRequest.*
    from DailyRequest
        right outer join Facilities on FacilityID = Facilities.ID 
            and DailyRequest.RequestDate = '7/21/2021'