Converting Informix OUTER statement to SQL Server

[suess]

I'm a newb to Informix and trying to convert the following statement to SQL Server (2005). The problem is the join. From what I've been able to find, it appears that Informix outer joins are left outer joins by default, so I'm pretty sure a "LEFT OUTER JOIN" needs to be there somewhere. The problem is the rest of the join syntax. Can anyone help?

Informix query:

SELECT it.lnum, it.vid, it.cc 
FROM it, vt, vr, OUTER hi, ac
WHERE it.vid = vr.vid
AND it.cc = vt.cc 
AND it.lnum = hi.lnum 
AND it.lnum = ac.lnum   
 

table name: field names are:
it: lnum, vid, cc
vt: vid, cc
vr: vid, cc
hi: lnum
ac: lnum

Everything is straightforward except for the FROM statement.

Thanks

[ck9663]

I'm a newb to Informix and trying to convert the following statement to SQL Server (2005). The problem is the join. From what I've been able to find, it appears that Informix outer joins are left outer joins by default, so I'm pretty sure a "LEFT OUTER JOIN" needs to be there somewhere. The problem is the rest of the join syntax. Can anyone help?

Informix query:

SELECT it.lnum, it.vid, it.cc 
FROM it, vt, vr, OUTER hi, ac
WHERE it.vid = vr.vid
AND it.cc = vt.cc 
AND it.lnum = hi.lnum 
AND it.lnum = ac.lnum   
 

table name: field names are:
it: lnum, vid, cc
vt: vid, cc
vr: vid, cc
hi: lnum
ac: lnum

Everything is straightforward except for the FROM statement.

Thanks

based on your query IT, VT and VR seems to be an INNER JOIN while HI and AC are LEFT JOIN. if my assumptions are correct,try this:

SELECT it.lnum, it.vid, it.cc 
FROM it
INNER JOIN vt on it.cc = vt.cc 
INNER JOIN vr on it.vid = vr.vid
LEFT JOIN hi on it.lnum = hi.lnum 
LEFT JOIN ac on it.lnum = ac.lnum
 

just a note: your columnlist does not include any columns coming from the other table. your left join will not mean anything if that's the case. your inner join will work, though...