1

I have 2 tables: Employees, attendanceIn enter image description here

When I query using LEFT JOIN

SELECT employees.eno,employees.name,employees.dept,attendanceIn.attIn FROM `employees` 
LEFT JOIN attendanceIn ON employees.eno = attendanceIn.eno
WHERE date(attIn) like '2016-07-02%'

What I got is,

Actual result

  • Whats wrong with my Query?
  • How do I get my Expected result?
8

The WHERE date(attIn) like '2016-07-02%' is converting the LEFT join to an INNER join. The condition should be moved to the ON clause.

Also:

  • It's not good practise to use LIKE for dates comparison
  • Using functions on columns (like the date()) before comparing it makes indexes useless. It's better to make the condition sargable.

The query corrected:

SELECT e.eno, e.name, e.dept, a.attIn 
FROM employees AS e 
  LEFT JOIN attendanceIn AS a 
    ON  e.eno = a.eno
    AND a.attIn >= '2016-07-02'
    AND a.attIn  < '2016-07-03' ;

Tested at SQLfiddle.

1

Your LEFT JOIN delivers the missing attendanceIn fields as NULL. If you still want this result then you must add:

or attIn is NULL

at the end of your query. Now you will get the expected result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.