i would like to ask question regarding left join and group by since examples in documentation are more of the basic one, i am looking for a combine group by with where clause that fits the needs that i wish to display
my tables are
employee table
| emp_id | lastname | firstname |
+-------+-----------+-----------+
| sgi01 | Doe | John |
| sgi02 | Doe | Karen |
| sgi03 | Doe | Vincent |
| sgi04 | Doe | Kyle |
+--------+-------+--------------+
time_records table
| emp_id | date |time_in |time_out |
+--------+----------+---------|----------|
| sgi01 |2024-03-01|07:00:00 |16:03:22 |
| sgi02 |2024-03-01|07:00:00 |16:03:22 |
+--------+----------+---------+----------+
Result Table
| emp_id | lastname | firstname | time_in|time_out|Ctime_in |Ctime_out |number_of_hrs
+-------+-----------+-----------+--------+--------+-----------+-----------+-------------+
| sgi01 | Doe | John |07:00:00+16:03:22|07:00:00 AM|04:03:22 PM|09:03:22
| sgi02 | Doe | Karen |07:00:00+16:03:22|07:00:00 AM|04:03:22 PM|09:03:22
+-------+-------+---------------+-------------------------------------------------------+
MYSQL CODE
SELECT e.emp_id, e.lastname, e.firstname, T.time_in, t.time_out ,
TIME_FORMAT(time_in, "%r") as Ctime_in,
TIME_FORMAT(time_out, "%r") as Ctime_out,
TIMEDIFF(time_out,time_in) as number_of_hrs
FROM `employees` as e LEFT JOIN time_records as t on e.emp_id = t.emp_id
where t.date = '2024-03-01'
GROUP by e.emp_id;
Desired or want to display like this
| emp_id | lastname | firstname | time_in|time_out |Ctime_in |Ctime_out |number_of_hrs
+-------+-----------+-----------+---------+---------+-----------+-----------+----------+
| sgi01 | Doe | John |07:00:00 |16:03:22 |07:00:00 AM|04:03:22 PM|09:03:22
| sgi02 | Doe | Karen |07:00:00 |16:03:22 |07:00:00 AM|04:03:22 PM|09:03:22
| sgi03 | Doe | Vincent |Null or 0|Null or 0|Null or 0 |Null or 0 |Null or 0|
| sgi04 | Doe | Kyle |Null or 0|Null or 0|Null or 0 |Null or 0 |Null or 0|
+-------+-------+---------------+-------------------------------------------------------+
Normally i would just use group by and left join to force the other record to view as null but when i try to include a date range it gives me only the record that has data..
>Solution :
The problem is the other employees do not have record in in time_records but you have this in your condition:
where t.date = '2024-03-01'
To include them even without records in time_records, change it to below:
where t.date = '2024-03-01' OR t.date IS NULL