SQL Left Join not including desired rows

Question

I'm trying to join 2 tables, where all data in the left table appears and only data that matches in the right table appears - should be a simple LEFT JOIN. But I either get results where only data in both tables appear if they match, or some of the data in left table and a correct set of data that matches in the right table.

Here's the code:

SELECT 
    tblCalendar.dDate, tblCalendar.dDay, 
    tblDailyData.TradeableDate, tblDailyData.TradeableCode
FROM 
    tblCalendar 
LEFT JOIN 
    tblDailyData ON tblCalendar.[dDate] = tblDailyData.[TradeableDate]
WHERE 
    (((tblDailyData.TradeableCode) = "MSFT")) 
     OR (((tblDailyData.TradeableDate) IS NULL) 
         AND ((tblDailyData.TradeableCode) IS NULL))
ORDER BY 
    tblCalendar.dDate;

Correct output should be (see image):

• all tblCalendar.dDate and tblCalendar.dDay rows
• and only the rows where tblDailyData.TradeableDate and tblDailyData.TradeableCode correspond to the tblCalendar days.

I've been trying to figure this out for hours. I've tried ChatGPT, resources here, numerous online resources, and the query builder in MS Access. I suspect I'm doing something wrong with the WHERE and ON clauses, but I can't figure out what.

Appreciate any help the group could provide.

Answer 1

Without seeing the data, it's hard to determine the core issue. These two lines, would only join if you had a NULL in tblCalendar.dDate. A simple test in your query would be to do a FULL OUTER JOIN instead of a LEFT JOIN.

     OR (((tblDailyData.TradeableDate) IS NULL) 
         AND ((tblDailyData.TradeableCode) IS NULL))

If it's necessary to get NULL tblDailyData.TradeableDate, you could handle it with a RIGHT JOIN to tblCalendar as a set, then FULL OUTTER JOIN from tblCalendar to that set.

WITH filteredDailyData AS(
SELECT
    tblDailyData.TradeableDate ,
    tblDailyData.TradeableCode
FROM tblDailyData
WHERE
    tblDailyData.TradeableCode = "MSFT"
    OR (tblDailyData.TradeableDate IS NULL 
        AND tblDailyData.TradeableCode IS NULL)
),
filteredCalendarDailyData AS(
SELECT
   tc.dDate,
   tc.dDay,
   fdr.TradeableDate,
   fdr.TradeableCode
FROM filteredDailyData AS fdr
RIGHT JOIN tblCalendar AS tc ON
   fdr.TradeableDate = fdr.dDate
),
fullCalendarFilteredDailyData AS(
SELECT
   c.dDate,
   c.dDay,
   fcdd.TradeableDate,
   fcdd.TradeableCode
FROM tblCalendar AS c
FULL OUTER JOIN filteredCalendarDailyData AS fcdd ON
   c.dDate = fcdd.dDate

)
SELECT * FROM fullCalendarFilteredDailyData

Answer 2

Move the filtering to a subquery:

SELECT 
    tblDailyData.TradeableDate, 
    tblDailyData.TradeableCode
FROM 
    tblDailyData
WHERE 
    (tblDailyData.TradeableCode = "MSFT") 
        OR 
    (tblDailyData.TradeableCode IS NULL 
    AND 
    tblDailyData.TradeableDate IS NULL); 

Save it as, say, qryDailyData.

Then run this query:

SELECT 
    tblCalendar.dDate, 
    tblCalendar.dDay, 
    qryDailyData.TradeableDate, 
    qryDailyData.TradeableCode
FROM 
    tblCalendar 
LEFT JOIN 
    qryDailyData ON tblCalendar.[dDate] = qryDailyData.[TradeableDate]
ORDER BY 
    tblCalendar.dDate;