LEFT JOIN mysql Filter non-existent records NOT IN

Question

Good afternoon, what I’m trying to do is this: have two tables

machines - stores copier location data ( address, zip code, etc ).

readings - stores machine code, counters, datareading, etc

I wanted to show off all the table records MACHINES that were not in the table readings in the given period;

I’m not making the right JOIN, if I put the interval, usually it will filter only machines that are within that range, but I want the opposite. I want machines that are not in that gap.

I’m having trouble using the WHERE datareading NOT BETWEEN data1 AND data2

SELECT maquinas.codigo, maquinas.codnovo
FROM leituras RIGHT JOIN maquinas
ON (leituras.CodigoMaquina = maquinas.Codigo)
where inativa=0
AND dataleitura>'2015-02-11' and dataleitura<'2015-03-17'
and codigodono=5

Who can give a force there. Thanks

As of 4:27 pm: What I need is to list ALL machines that DO NOT have readings in the table in the specified period

As of 4:48 pm This almost solves what I want, I’m trying to use a having here to see if it will fit.

SELECT maquinas.codigo, maquinas.codnovo, MAX(leituras.dataleitura) as ultLcto
FROM
maquinas INNER JOIN leituras ON maquinas.codigo=leituras.codigomaquina
WHERE inativa=0 AND codigodono=5
GROUP BY maquinas.codigo
ORDER BY codigomaquina

Answer 1

This image can help. Our case is the second from left, from top to bottom. Try the following:

SELECT maquinas.codigo, maquinas.codnovo
FROM maquinas
LEFT JOIN leituras ON
    maquinas.Codigo = leituras.CodigoMaquina
    AND dataleitura > '2015-02-11' AND dataleitura < '2015-03-17'
WHERE leituras.CodigoMaquina IS NULL
AND inativa = 0    
AND codigodono = 5