0

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?

BLAKE
  • 149
  • 1
  • 15

0 Answers0