Is the join expression not supported by MS Access?

Question

Can anyone explain to me what is wrong with my query?

SELECT T2.TIPOPRODUTO
    , T2.PRODUTO
    , T1.ESPESSURA
    , '' AS LARGURA
    , '' AS COMPRIMENTO
    , '' AS [ACABAM REVEST]
    , '' AS [ESPECIF QUALIDADE]
    , T1.CÓDIGORASTREABILIDADE
    , T3.DATA
    , T4.NOMEFANTASIA
    , T7.NOME
    , T5.DT_INICIO_RESERVA
    , T1.PESO
    , T5.DT_FIM_RESERVA
    , '' AS DESTINO
    , T3.OBSERVAÇÃO
    , '' AS [CUSTO TOTAL]
FROM ([TABELA DE PRODUTOS/ESTOQUE] LEFT OUTER JOIN [TABELA DE PRODUTOS] ON ([TABELA DE PRODUTOS/ESTOQUE].PRODUTO=[TABELA DE PRODUTOS].ID))
, [TABELA DE PRODUTOS/ESTOQUE ] AS T1
    , [TABELA DE PRODUTOS] AS T2
    , [TABELA DE MOVIMENTAÇÃO DE ESTOQUE] AS T3
    , [TABELA DE FORNECEDORES] AS T4
    , RESERVAS_PRODUTOS_ESTOQUE AS T5
, [TABELA DE MOVIMENTAÇÃO DE PRODUTOS/ESTOQUE] AS T6
    , [TABELA DE USUÁRIOS] AS T7
    , [TABELA DE PEDIDOS DE COMPRA] AS T8
WHERE (((T1.Produto)=[T2].[ID]) 
    AND ((T1.ID)=[T5].[ID_PRODUTO_ESTOQUE]) 
    AND ((T5.id_vendedor)=[T7].[ID]) 
    AND ((T3.ID)=[T6].[ID]) 
    AND ((T2.ID)=[T6].[PRODUTO]) 
    AND ((T4.ID)=[T8].[FORNECEDOR]) 
    AND ((T8.Comprador)=[T7].[ID]));

My best guess is it fails on this line:

([TABELA DE PRODUTOS/ESTOQUE] LEFT OUTER JOIN [TABELA DE PRODUTOS] ON ([TABELA DE PRODUTOS/ESTOQUE].PRODUTO=[TABELA DE PRODUTOS].ID))

This is a somewhat odd looking query in that you have a number of tables that are not joined with anything. As far as i know, the only join that will work with the set-up you have is an INNER JOIN. However, you may need to rethink your design. — Fionnuala, Mar 16 '09 at 15:00
LEFT OUTER JOIN is not Jet SQL syntax. It uses LEFT JOIN and RIGHT JOIN, but omitting the "OUTER." I can't say if it's 100% equivalent to what you get in other SQL dialects. — David-W-Fenton, Mar 18 '09 at 00:47
@David W. Fenton: did you test what you assert? I tested in Jet 3.51, Jet 4.0 and ACE (2007) and LEFT OUTER JOIN works on all. If you don't test your SQL, it's just not useful to post it as an answer, in my opinion. — onedaywhen, Mar 19 '09 at 08:24

score 8 · Accepted Answer · answered Mar 16 '09 at 15:12

You are mixing a join statement with "classical joins" (a comma separated list of tables with conditions in the where statement), which I believe is not allowed.

Change the query to use only join statements. In Access you have to pair the joins using parentheses, in this manner:

from (((t1 join t2 on ...) join t3 on ...) join t4 on ...)

Is the join expression not supported by MS Access?

1 Answers1

Linked