Linesofcode Linesofcode - 1 year ago 98
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,

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
has not yet left the office and the employee
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, presence.movement
FROM employees AS employee
LEFT JOIN presences AS presence ON presence.employee_id =
WHERE presence.presence_date = '2016-08-30' AND IN (1, 2, 3, 4)

The problems I'm facing:

  1. Joseph never appears in the data

  2. DESC
    doesn't work

Answer Source
  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, max(presence.movement) as movement
FROM employees AS employee
LEFT JOIN presences AS presence ON presence.employee_id = and date(presence.presence_date) = '2016-08-30'
WHERE IN (1, 2, 3, 4)
GROUP BY, date(presence.presence_date)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download