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.