Unable to get a join to pull all records from another table in MySQL [duplicate]

Question

I've got this working in MSSQL but I've fallen off and been run over when it comes to MySQL. I'm trying to search for all records in a group after a certain date in an employee info table. I can get the records in the employee info table but I also need to return all employee numbers in another table that are part of the same group. The purpose being to see which employees in the group have submitted some paperwork. Since the table has data from multiple years, it needs to filtered so I can search after a certain date each year.

Here are my two tables

empInfo
--------------------------------------
id             | int
date           | datetime
empNumber      | varchar
grpNumber      | int
active         | int

empReports
--------------------------------------
id             | int
date           | datetime
empNum         | varchar
grpNumber      | int
empFName       | varchar
empLName       | varchar
dateReceived   | varchar
active         | int

The idea is to search for a group number and return all employee IDs from the empInfo table and show which ones have records from the empReports table. The current query is:

SELECT r.dateReceived as Date,
t.empNumber AS Emp,
r.grpNumber AS Grp,
r.empFName,
r.empLName 
FROM empInfo t 
LEFT JOIN empreports r 
ON t.empNumber = r.empNum
WHERE t.grpNumber = 100 
AND t.active = 1 
AND r.dateReceived > 1587574487 
ORDER BY CAST(t.empNumber as UNSIGNED INTEGER) ASC

This returns a single record

    Date    |  Emp  |  Grp  |  empFName  |  empLName
------------------------------------------------------
1589561752  |   1   |  100  |  Fred      |  Derf

What I need to see is more like

    Date    |  Emp  |  Grp  |  empFName  |  empLName
------------------------------------------------------
1589561752  |   1   |  100  |  Fred      |  Derf
    NULL    |   2   |  100  |  NULL      |  NULL         
    NULL    |   3   |  100  |  NULL      |  NULL  
    NULL    |   4   |  100  |  NULL      |  NULL  
    NULL    |   5   |  100  |  NULL      |  NULL  

I've tried different joins, swapping the query around and just about anything else I can think of with no joy, so any assistance any MySQL gurus could offer would be greatly appreciated.

Thanks in advance.

Answer 1

The problem is this:

If r.dateReceived is NULL, the WHERE r.dateReceived > 1587574487 is never satisfied

What you need to do is check for r.dateReceived in the WHERE clause as well

SELECT r.dateReceived as Date,
t.empNumber AS Emp,
r.grpNumber AS Grp,
r.empFName,
r.empLName 
FROM empInfo t 
LEFT JOIN empreports r 
ON t.empNumber = r.empNum
WHERE t.grpNumber = 100 
AND t.active = 1 
AND (r.dateReceived > 1587574487 OR r.dateReceived IS NULL)
ORDER BY CAST(t.empNumber as UNSIGNED INTEGER) ASC

GIVE IT A TRY !!!