Comma join syntax (from ,...,..,... where ... and ...) is archaic, and IAC doesn't work correctly with explicit join syntax in MySQL. Change the comma joins to explicit join syntax.
Change IN(...) to a join, and NOT IN(...) to a (left) exclusion join, eg ...
select
numero,
date_format(c1.dateaffr,'%d/%m/%y'),
c1.cdtypenvoi,
c1.cdpost,
e.lib,
concat(l.nidf,' - ',c.lib,' - ',p.lib,': ',rtrim(p.libpres),' ',
t.poids,' - datefacture: ',date_format(l.datefacture,'%d/%m/%y')
)
from lot l
join eta e on e.ver_id = l.ver_id
join version v on l.ver_id = v.id
join client c on v.client_id = c.id
join pro p on l.cdpro = p.cd and p.ver_id = l.ver_id
join typelot t on l.typelot_id = t.id
join contenant1 c1 on l.id = c1.lot_id
left join eta on c1.cdeta = eta.cd
left join (
select c1.numero
from expedition ex
join contenant2 c2 on ex.id = c2.exped_id
join contenant1 c1 on c2.numero = c1.numcont2
) x on c1.numero=x.numero
where l.id_cisif=3
and l.cdstatut in(3,2)
and c1.cdstatut in(9,10)
and x.numero is null
and c1.numero <> '000000'
order by 6, 1;
It has duplicate references to the contenant1 table, and that's not efficient. Also it joins to eta twice, do you really mean to do that? But the above should get you started as an expression of the logic you're trying to port to MySQL.
Once the logic has been shown to work, you'll need to optimise it.
Doubtless there are mistakes in the above, I don't have your tables, but it should get you started.
Edited 1 time(s). Last edit at 04/11/2014 03:26PM by Peter Brawley.