I'm trying to join three tables in an external odbc database and am having problems with joining them.



Here is the sql query I have as the data connection.

SELECT item.item_cd, item.item_desc, inv.inv_price_1, item_trsl.ittr_alt_cd, item.item_grp_nbr, item.item_sect_nbr, inv.loc_id

FROM informix.inv inv, informix.item item, informix.item_trsl item_trsl

WHERE inv.item_id = item.item_id AND item.item_cd = item_trsl.ittr_item_cd AND ((inv.loc_id=1)

The problem is that not every item_cd will have an ittr_alt_cd so what I need is a left join but I can't seem to do it.

Here is what i can do in Access for the same tables

SELECT informix_item.item_cd, informix_item.item_desc, informix_inv.inv_price_1, informix_item_trsl.ittr_alt_cd, informix_item.item_gl_nbr, informix_item.item_sect_nbr, informix_inv.loc_id

FROM (informix_item INNER JOIN informix_inv ON informix_item.item_id = informix_inv.item_id) LEFT JOIN informix_item_trsl ON informix_item.item_cd = informix_item_trsl.ittr_item_cd

WHERE (((informix_inv.loc_id)=1));


This works like expected but I can't seem to translate this sql query to the excel side. If I try to edit the query using the graphical interface i get "Can't have outer joins if there are more than two tables in the query' and if I try and modify the sql code itself I get things like 'Table (informix.inv) not selected in the query.

Help!