craisondigital craisondigital - 8 months ago 22
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,,, TIME_FORMAT(punches.tistamp, '%H:%i') AS time
FROM employees, punches
GROUP BY emp_id

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


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 AND = (SELECT MAX( FROM punches p WHERE ORDER BY e.last ASC