virrion virrion - 3 months ago 8
MySQL Question

Mysql select - all from left and only matching right table

I have two tables:


  1. employees


    [id, first_name, last_name, status]

  2. absence


    [id, employee_id, reason_type, date_from, date_to



I can do something like:

SELECT e.first_name, e.last_name, e.status, a.reason_type FROM employees e JOIN absence a ON e.id=a.employee_id
WHERE curdate() between date_from and date_to


but it will give me only those employees that are found in
absence
table.

Is there a way to get a list of ALL employees and their status (for those found in
absence
table matching condition
currdate() between date_from and date_to
return 'Yes' and
reason_type
) and for others say No and null for
reason_type
.

Thanks.

Answer

You are looking for a left join, but you have to be careful about the where clause:

SELECT e.first_name, e.last_name, e.status, a.reason_type
FROM employees e LEFT JOIN
     absence a
     ON e.id = a.employee_id AND
        curdate() between a.date_from and a.date_to ;