craisondigital craisondigital - 4 months ago 7
SQL Question

SQL query to Return latest entry per user in mysql utilizing two tables

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
WHERE punches.emp_id=employees.id
GROUP BY emp_id
ORDER BY emp_id DESC


The punches table has an 'id' field which auto increments, so essentially I am looking for the max punches.id for each employee.

Answer

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
Comments