MYSQL not showing results on left join with id condition-CodePudding

I have two tables one named attandances to store punch in and out record AND other one is Calender where I have all the dates Attandances:

Calender:

I want to fetch those dates of month where employee haven't punched in my query is as follow:

select a.employeeID, c.cdate
from calendar c
    left join attandances a on (c.cdate = a.date)
where a.date is null
and a.employeeID='1'
and c.cdate between '2021-12-01' and '2021-12-31'

But when I execute , it returns me 0 rows. Any suggestion what I am missing?

CodePudding user response：

where a.date is null
and a.employeeID='1'

These cannot both be true. If the left join finds no records, date will be null and employeeID will also be null. If it does find a record, date will be not null. It looks to me like you just want to move the employeeID condition to your join clause:

from calendar c
    left join attandances a on c.cdate = a.date and a.employeeID='1'
where a.date is null
and c.cdate between '2021-12-01' and '2021-12-31'

CodePudding user response：

I think where you do

from calender c

should be

from calender

and then also

left join attandances a

should be

left join attandances on calender.cdate = attandances.date

CodePudding user response：

I would like to receive some data load script, but because of the visualization in the images you provided, the query will never return results.

You are doing a JOIN between both tables by the columns (c.cdate = a.date)and then within the WHERE statement there are two opposite conditions, on the one hand a.date is null and for another c.cdate between '2021-12-01' and '2021-12-31', remember that when making a JOIN, the columns by which the tables will be associated are expected to behave in the same way same way, so you can't set opposite conditions between their values.

The query you are trying to get is similar to this in my opinion:

select DISTINCT calendar.cdate
from calendar
where calendar.date NOT IN (
        SELECT attandances.date
        FROM attandances
        WHERE attandances.employeeID = 1
    ) AND calendar.cdate between '2021-12-01' and '2021-12-31'