MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.7.27


CREATE TABLE personnel (id INTEGER, name VARCHAR(30));
INSERT INTO personnel VALUES (1, 'Joe'), (2, 'Mary'), (3, 'Bill'), (4, 'Fred')
SELECT * FROM personnel;


id
name


1
Joe

2
Mary

3
Bill

4
Fred


CREATE TABLE timecard (empid INTEGER, clockin_time DATETIME);
INSERT INTO timecard VALUES (1, '2019-08-28 08:00:18');
INSERT INTO timecard VALUES (2, '2019-08-28 08:00:28');
INSERT INTO timecard VALUES (3, '2019-08-28 08:00:28');
INSERT INTO timecard VALUES (1, '2019-08-29 08:00:03');
INSERT INTO timecard VALUES (4, '2019-08-29 08:04:04');
SELECT * FROM timecard
ORDER BY clockin_time, empid;


empid
clockin_time


1
2019-08-28 08:00:18

2
2019-08-28 08:00:28

3
2019-08-28 08:00:28

1
2019-08-29 08:00:03

4
2019-08-29 08:04:04


SELECT p.id AS the_id -- , p.name, t.empid -- remove -- to see results - shows thought process!
FROM timecard t
LEFT JOIN personnel p
  ON t.empid = p.id
WHERE DATE(t.clockin_time) = '2019-08-29'
ORDER BY p.id, t.clockin_time;


the_id


1

4


SELECT * FROM personnel pl
WHERE pl.id NOT IN
(
  SELECT p.id AS the_id
  FROM timecard t
  LEFT JOIN personnel p
    ON t.empid = p.id
  WHERE DATE(t.clockin_time) = '2019-08-29'
  ORDER BY p.id, t.clockin_time
);


id
name


2
Mary

3
Bill

empid	clockin_time
1	2019-08-28 08:00:18
2	2019-08-28 08:00:28
3	2019-08-28 08:00:28
1	2019-08-29 08:00:03
4	2019-08-29 08:04:04