Outer Join with some other tables in FROM

Question

FROM [TABELA DE PRODUTOS/ESTOQUE] AS T1
, [TABELA DE PRODUTOS] AS T2
, [TABELA DE MOVIMENTAÇÃO DE ESTOQUE] AS T3
, [TABELA DE FORNECEDORES] AS T4
, [TABELA DE PRODUTOS/ESTOQUE] AS T5
WHERE (((T1.Produto)=[T2].[ID]) 
ETC

So, how can I add a JOIN between those tables ? I need a left join, like:

FROM [TABELA DE PRODUTOS/ESTOQUE] <- TABLE1
LEFT JOIN [TABELA DE MOVIMENTAÇÃO DE ESTOQUE] <- TABLE2
ON TABLE1.ID=TABLE2.ID

Obs: Table1 and Table2 I just added to explain

Thanks.

---

Do I have to use UNION ?

Answer 1

Your example shows how to create the join, so what exactly are you asking for?

Answer 2

No you don't have to use a union. If I understand your question correctly, it's as simple as combining things like this:

FROM      [TABELA DE PRODUTOS/ESTOQUE]            AS T1,
          [TABELA DE PRODUTOS]                    AS T2,
          [TABELA DE MOVIMENTAÇÃO DE ESTOQUE]     AS T3A,
          [TABELA DE FORNECEDORES]                AS T4,
          [TABELA DE PRODUTOS/ESTOQUE]            AS T5,
LEFT JOIN 
          [TABELA DE MOVIMENTAÇÃO DE ESTOQUE]     AS T3B ON T1.ID=T3B.ID
WHERE (((T1.Produto)=[T2].[ID])

Answer 3

FROM [TABELA DE PRODUTOS/ESTOQUE] AS TABLE1
LEFT JOIN [TABELA DE MOVIMENTAÇÃO DE ESTOQUE] AS TABLE2
ON TABLE1.ID=TABLE2.ID

Answer 4

I understand that you want to OUTER JOIN a table, without using the 'JOIN syntax' ?

What DBMS are you using ? It is possible to do this in the from clause, but the syntax differs from DBMS to DBMS.

For instance, in SQL Server, this is the syntax:

SELECT * 
FROM Table1, Table2
WHERE Table1.Id *= Table2.Id

In Oracle , on the other hand, you don't use an asterix but a (+) as far as i remember.

Anyway, I think it is clearer to use the JOIN-syntax (LEFT JOIN ... )

Answer 5

Maybe:

FROM [TABELA DE PRODUTOS/ESTOQUE] AS T1
, [TABELA DE PRODUTOS] AS T2
, [TABELA DE MOVIMENTAÇÃO DE ESTOQUE] AS T3
, [TABELA DE FORNECEDORES] AS T4
, [TABELA DE PRODUTOS/ESTOQUE] AS T5
WHERE (T1.Produto=T2.ID OR T2.ID IS NULL)
ETC

Answer 6

Provided you want to left join all tables you would do:

SELECT *
FROM [TABELA DE PRODUTOS/ESTOQUE] AS T1 
LEFT OUTER JOIN [TABELA DE PRODUTOS] AS T2 ON T1.Key = T2.Key
LEFT OUTER JOIN [TABELA DE MOVIMENTAÇÃO DE ESTOQUE] AS T3 ON T2.Key = T3.Key
LEFT OUTER JOIN [TABELA DE FORNECEDORES] AS T4 ON T3.Key = T4.Key
LEFT OUTER JOIN [TABELA DE PRODUTOS/ESTOQUE] AS T5 ON T4.Key = T5.Key

I think you have to clarify your question in order to stop our guessing.

Answer 7

LFSR answered it, thanks for the help guys, I guess I wasn't that clear on my Q .. :P