I have two tables I need to join for the current date. If a unit number has no entry for the current date I need the values to be NULL
.
Table 1 (All Unit Numbers with their locations):
unitNumber | Location
-----------|---------
123 | IL
345 | CA
567 | MA
Table 2 (All entries):
unitNumber | Location | Date |
-----------|-------------------------
123 | IL | 2019-11-27
345 | CA | 2019-11-27
345 | CA | 2019-11-26
567 | MA | 2019-11-25
Need the query to display something like (Current Date is 2019-11-27) :
unitNumber | Location | Date |
-----------|-------------------------
123 | IL | 2019-11-27
345 | CA | 2019-11-27
567 | NULL | NULL
The unit number 567 needs to have NULL
values for Location
and Date
because it is not in the entry table for the day.
I have tried to use the query:
SELECT * FROM table1 LEFT JOIN table2 ON table1.unitNum=table2.unitNumber WHERE table2.Date = CURDATE() OR table2.Date IS NULL
But this only returns the entries that have been filled out for the current date. And does not return the NULL entries.
Question: How should I tweak this query so that the query will pull the unit number in table1 and display NULL values if they are not in table 2 for the current date?