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,
ID | name
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
Employee | Movement
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
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.
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)