0

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.

craisondigital
  • 69
  • 1
  • 9
  • Use `Join` instead of a `Where` and show desired output in a table format, – Igoranze Jul 07 '16 at 14:54
  • Add both tables schema – Daniel Shalev Jul 07 '16 at 15:02
  • I tried changing WHERE to JOIN, but received a mysql error. – craisondigital Jul 07 '16 at 15:25
  • I found that this works.. "SELECT m1.* FROM punches m1 LEFT JOIN punches m2 ON (m1.emp_id = m2.emp_id AND m1.id < m2.id) WHERE m2.id IS NULL", but it cannot figure out how to get the info from the related table with this.. – craisondigital Jul 07 '16 at 15:25
  • Why the group by clause? What are you aggregating? – Darthtater Jul 07 '16 at 20:04
  • I'm trying to create a webpage(php) to easily show the current status of employees (wheather they are clocked in, on break, or clocked out) I'm using GROUP BY to only show the one record per employee. – craisondigital Jul 08 '16 at 17:37
  • I have found a query that works. Here it is if anyone comes accross this.. – craisondigital Jul 08 '16 at 19:37
  • 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 – craisondigital Jul 08 '16 at 19:37

1 Answers1

0

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
craisondigital
  • 69
  • 1
  • 9