LEFT JOIN mysql Filter non-existent records NOT IN

Question

LEFT JOIN mysql Filter non-existent records NOT IN

Navigation

#1 by (2 votes)

1

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

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

readings - stores the machine code, counters, date, etc.

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

I'm not doing JOIN, if I put the interval, it will normally filter only machines that are within that range, but I want the opposite. I want machines that are not in that range.

I am not able to use the WHERE databinding 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

If you can, give it a boost. Thanks.

Editing at 16:27: What I need, is to list ALL machines that do NOT have table readings in the specified period

Edition at 16:48 This here 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

mysql date

asked by anonymous 25.03.2015 / 20:12

1 answer

List tables by foreign key in MySQL POO PHP function insert into MYSQL [closed]

score 2 · Answer 1

This image can help. Our case is the second from the 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