Left join from TSQL to MySQL syntax

[Charles Delaunois]

Hello,

I'm stuck on a querry that as been translated from sql using T-SQL (with SQL Server 2005) to MySQL. In the original one, there was a short syntax "*" for a right join, that I am uncapable to translate/express in the MySQL way querry.

Here's the original one, from MS SQL:

SELECT numero,convert(varchar,c1.dateaffr,103),c1.cdtypenvoi, c1.cdpost, e.lib, l.nidf + '  -  ' +c.lib  + '  -  ' + p.lib + ': ' + 
rtrim(p.libpres) + ' ' + t.poids  + ' -  dateFacture: '+ convert(varchar,l.datefacture,103)  
FROM  contenant1 c1, lot l, eta e, version v, client c, pro p , typelot t  
WHERE l.id 	= c1.lot_id  AND   l.ver_id = v.id  AND   v.client_id = c.id  
AND   l.cdpro = p.cd  AND   l.typelot_id = t.id  AND   p.ver_id = l.ver_id  AND   l.cdstatut in ( 3,2 )  
AND   c1.cdstatut in ( 9,10 )  AND   e.cd =* c1.cdeta  
AND   e.ver_id in (select e2.ver_id from eta e2 where e.ver_id  = l.ver_id)   
AND   numero not in (  	select  c1.numero  	from expedition ex ,contenant2 c2, contenant1 c1  	
where  ex.id = c2.exped_id  	AND   c2.numero = c1.numcont2  ) AND numero <> '000000'  ORDER BY 6, 1

Note the

e.cd =* c1.cdeta

to express the right join...

The same querry translated to MySQL :

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  contenant1 c1, lot l, eta e, version v, client c, pro p , typelot t  
WHERE l.id    = c1.lot_id  AND   l.ver_id = v.id AND l.id_cisif =3 AND   v.client_id = c.id  
AND   l.cdpro = p.cd  AND   l.typelot_id = t.id  AND   p.ver_id = l.ver_id  AND   l.cdstatut in ( 3,2 )  
AND   c1.cdstatut in ( 9,10 )  AND   e.cd      = c1.cdeta  AND   e.ver_id  = l.ver_id   
AND   numero not in (         select  c1.numero       from expedition ex ,contenant2 c2, contenant1 c1        
where  ex.id = c2.exped_id      AND   c2.numero = c1.numcont2  ) AND numero <> '000000'  ORDER BY 6, 1;

It does work, but some rows from contenant1 are omittided, since the right join between eta and contenant1 is not expressed.

I tried many things like

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, eta e, version v, client c, pro p , typelot t,contenant1 c1
left join  eta on c1.cdeta = eta.cd
WHERE l.id    = c1.lot_id  AND   l.ver_id = v.id AND l.id_cisif =3 AND   v.client_id = c.id  
AND   l.cdpro = p.cd  AND   l.typelot_id = t.id  AND   p.ver_id = l.ver_id  AND   l.cdstatut in ( 3,2 )  
AND   c1.cdstatut in ( 9,10 )   AND   e.ver_id  = l.ver_id   
AND   numero not in (         select  c1.numero       from expedition ex ,contenant2 c2, contenant1 c1        
where  ex.id = c2.exped_id      AND   c2.numero = c1.numcont2  ) AND numero <> '000000'  ORDER BY 6, 1;

None worked ; I'm quite confused with how to express outer joins with several tables and inner joins in wher clause

for info, contenant1 table has 31811 rows and eta rows has 18450 rows.

select version();
+------------+
| version() |
+------------+
| 5.5.14-log |
+------------+

Any clue on this, very welcome.

Regards



Edited 1 time(s). Last edit at 04/14/2014 01:43AM by Charles Delaunois.