THIS IS NOT A DUPLICATE.. I am working with two tables. The "Answer" users are linking to only utilizes one table. I need to return the latest entry utilizing RELATED tables. I have not found a solution which does this.
I'm working on a time clock. I have two tables in my db called employees and punches.. The punches table has a field called 'emp_id' which relates to the 'id' field in the employees table. I'm trying to figure out a SQL query which will return the latest entry for each employee (Their current status, whether they are currently clocked IN, OUT, BREAK, etc.)
The answers I have found (and are referenced in the comments) are not pulling the info from two tables, just one.
So far, I have the following, but this only returns the first punch for each employee. I am trying to return the latest punch instead.
SELECT CONCAT(employees.first, ' ', 'employees.last) AS name, punches.id, punches.date, TIME_FORMAT(punches.tistamp, '%H:%i') AS time
FROM employees, punches
GROUP BY emp_id
ORDER BY emp_id DESC
I found that this code works..
SELECT p.emp_id, CONCAT(e.last, ', ', e.first) AS name, p.status, DATE_FORMAT(p.tistamp, '%d/%m/%Y') AS date, TIME_FORMAT(p.tistamp, '%H:%i') AS time FROM punches p, employees e WHERE p.emp_id=e.id AND p.id = (SELECT MAX(p.id) FROM punches p WHERE e.id=p.emp_id) ORDER BY e.last ASC