Mysql query error

0

Hello!

Why is the status coming different from the table?

follow my query:

SELECT DISTINCT max(idEmbreagem) as idEmbreagem, b.prefixo as idVeiculo, max(datamontagem) as datamontagem, max(kmmontagem) as kmmontagem, max(horimetromontagem) as horimetromontagem, max(c.km) as kmatual, max(c.horimetro) as horimetroatual, a.'status', max(tipoembreagem) as tipoembreagem, (max(c.km) - max(a.kmmontagem)) as kmrodados FROM embreagem a
              JOIN veiculos b on (a.idVeiculo=b.idVeiculo)
              JOIN hodometro c on (a.idVeiculo=c.idVeiculo)
              GROUP BY a.idVeiculo order by idVeiculo ASC
    
asked by anonymous 24.06.2018 / 05:52

3 answers

2

Friend, ditinct with max ... Something looks really bad there ... = /

The fact is that the distinct and MAX () 's are messing up the results, shuffling them ... You should think better of putting this query together.

    
24.06.2018 / 13:53
1

Because you are bringing several max in the query, it is shuffling the results with max of each select following the conditionals.

    
24.06.2018 / 15:31
0

If you want to get the latest data from the embreagem and hodometro tables, do so:

SELECT b.idembreagem, a.prefixo idveiculo, a.datamontagem,
       a.kmmontagem, a.horimetromontagem, c.km kmatual,
       c.horimetro horimetroatual, b.status, b.tipoembreagem,
       (c.km - a.kmmontagem) as kmrodados
FROM veiculos a
JOIN embreagem b ON b.idveiculo = a.idveiculo
JOIN hodometro c ON c.idveiculo = a.idveiculo
WHERE b.idembreagem IN (SELECT MAX(idembreagem) FROM embreagem GROUP BY idveiculo)
AND c.idhodometro IN (SELECT MAX(idhodometro) FROM hodometro GROUP BY idveiculo)
ORDER BY a.prefixo ASC
    
25.06.2018 / 04:11