0

The user table contains users' information. The clock_in table contains the clockin of the users every day. I have been trying to check who is absent or not yet clocked in.

For example by 9:00 am, I should be able to check who has not clocked in yet. I have came up with the MySQL query and this what I have come up with so far after about 7 days now searching. The idea is to compare the user table to the users clockin today, if there is an unmatched (IS NULL) then that user has not clocked in today. Here is my updated query so far.

SELECT users.* 
FROM users  
LEFT OUTER JOIN timecard  
  ON users.id = timecard.employment_id 
AND timecard.date='".$today."'
WHERE timecard.employment_id IS NULL and user.location = '".$location."' 
ORDER BY users.dept DESC

Please, I know Left Outer Join may be the right one according to the results of my search, need your advice

6
  • It looks to me that t1.id will never be NULL. You should be JOINing against a table with the dates of working days!
    – Vérace
    Aug 27 '19 at 3:11
  • Thanks Verace, I will try...
    – A. Kiyoshi
    Aug 27 '19 at 3:15
  • Check out this.
    – Vérace
    Aug 27 '19 at 3:19
  • p.s. welcome to the forum! :-)
    – Vérace
    Aug 27 '19 at 3:59
  • If $today is all you want, then just reverse your original join order.
    – Vérace
    Aug 27 '19 at 4:03
0

The best solution is something like this:

CREATE TABLE personnel (id INTEGER, name VARCHAR(30));

populate it:

INSERT INTO personnel VALUES (1, 'Joe'), (2, 'Mary'), (3, 'Bill'), (4, 'Fred');

Create and populate the timecard table:

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');

Note that only Joe and Fred have clocked in on the 29th of August - Mary and Joe haven't.

You can find the fiddle here.

So, first run this query to determine who clocked in on the 29th.

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;

Result:

the_id
     1
     4


So, we see that Joe (id 1) and Fred (id 4) have clocked in.

Now, we need to find out the identities of those who didn't clock in. This is the list of people whose id's are not in the result of the first query. We obtain this by doing the following:

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
);


Result:

id  name
 2  Mary
 3  Bill

Something like this is probably what you require - if not, let me know. This construction allows you to do tallies of how many times person with id x was absent in a given month, day, using BETWEEN and lots more.... fun for all the family! :-)

6
  • Thank you for the solution, just by reading the codes make sense. Amazing...I will get back to you ASAP
    – A. Kiyoshi
    Aug 28 '19 at 4:47
  • Thanks - if the solution is good for you, you could upvote it and/or mark it correct? :-)
    – Vérace
    Aug 28 '19 at 4:53
  • Thank Verace, Here is my query SELECT * FROM users WHERE users.id NOT IN (SELECT timecard.* FROM timecard LEFT JOIN users on timecard.employment_id = users.id WHERE timecard.date='".$today."' order by users.dept) an error " Operand should contain 1 column(s) " can you help me here please
    – A. Kiyoshi
    Aug 28 '19 at 5:25
  • If the timecard table has more than one field (i.e. id), I fail to see how your query could work. Copy my fiddle and run your first tests on that data. In the meantime, if you found my answer helpful, you can upvote it. You could also mark it as correct for the question as asked.
    – Vérace
    Aug 28 '19 at 5:33
  • Verace, I have updated my answer, it works but takes time to get the query from DB, what do you think?
    – A. Kiyoshi
    Aug 28 '19 at 5:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.