Linesofcode Linesofcode - 3 months ago 21
MySQL Question

MySQL retrieve employees presences

I need to retrieve the employees presences for the day. There are two states in the presences: In & Out.
If the employee does not have a presence it should retrieve with the status of null.

I have two tables,

Employees
and
Presences
and I want to join them.

ID | name
1 John
2 Julie
3 Anthony
4 Joseph


Now the presences table has the following data:

ID | employee_id | presence_date | presence_hour | Movement
1 1 2016-08-30 08:55 In
2 2 2016-08-30 08:56 In
3 3 2016-08-30 08:57 In
4 1 2016-08-30 12:33 Out
5 2 2016-08-30 12:34 Out


As you can see in the presences data, the employee
Anthony
has not yet left the office and the employee
Joseph
has no entries in the table.

The result I'm expecting:

Employee | Movement
John Out
Julie Out
Anthony In
Joseph null


The query I'm using:

SELECT employee.name, presence.movement
FROM employees AS employee
LEFT JOIN presences AS presence ON presence.employee_id = employee.id
WHERE presence.presence_date = '2016-08-30' AND
employee.id IN (1, 2, 3, 4)
GROUP BY employee.id
ORDER BY employee.name, presence.id DESC


The problems I'm facing:


  1. Joseph never appears in the data

  2. presence.id DESC
    doesn't work


Answer
  1. This is caused by applying the date filter in the where criteria. The where criteria is applied after the join, thus eliminating any records for Joseph, since he was not present that day. Move the date criteria to the join condition instead.

  2. You got the whole group by wrong, your query is against the sql standards because you have columns in the select list that are not in the group by list and are not subject of an aggregate function, such as max(). MySQL allows such queries under certain sql mode settings only. Use max() on the movement and group by on employee name and date fields.

Sample query, assuming you can only have 1 in and one out per employee per day:

SELECT employee.name, max(presence.movement) as movement
FROM employees AS employee
LEFT JOIN presences AS presence ON presence.employee_id = employee.id and date(presence.presence_date) = '2016-08-30'
WHERE employee.id IN (1, 2, 3, 4)
GROUP BY employee.name, date(presence.presence_date)