OUTER JOIN on more than two tables

Two questions:
1) Can an OUTER JOIN, such as a LEFT OUTER JOIN be done on more than two
tables and, if so, what is the syntax for it ?
I want to do a LEFT OUTER JOIN so that all rows from my left table are
selected which meet my where conditions, and where matching rows from
more than one other table are selected based on a match between my left
table and the other tables.
2) In the above scenario, one of the matches occurs between a column
value in my non-left table and a column value in another one of my
non-left tables. How can this also be specified in a LEFT OUTER JOIN
with more than one table in my LEFT OUTER JOIN syntax ?
Example:
Table1: ColumnA, ColumnB, other columns etc.
Table2: ColumnC, ColumnD
Table3: ColumnE, ColumnF
Table4: ColumnG, ColumnH
I want to join these tables such that all rows and columns from Table1
are selected matching my where condition. ColumnC is also selected when
ColumnA matches ColumnD, else ColumnC is null. ColumnE is also selected
when ColumnA matches ColumnF, else ColumnE is null. Finally ColumnG is
also selected when Table3's ColumnE matches ColumnH, else ColumnG is null.For example:
SELECT *
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.x = B.x
LEFT JOIN TableC AS C
ON B.x = C.x
If that doesn't answer the question then please post DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Edward,see it this helps you
SELECT
ColumnA,
ColumnB,
CASE
WHEN EXISTS (
SELECT ColumnC FROM Table2 t2
WHERE t2.ColumnD = table1.ColumnA)
THEN 1
ELSE 0
END AS ColumnC,
CASE
WHEN EXISTS (
SELECT ColumnE FROM Table3 t3
WHERE t3.ColumnF = table1.ColumnA)
THEN 1
ELSE 0
END AS ColumnE,
CASE
WHEN EXISTS (
SELECT ColumnG FROM Table4 t4
WHERE t3.ColumnH = table1.ColumnA)
THEN 1
ELSE 0
END AS ColumnE
FROM Table1
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:O656jo9UFHA.2036@.TK2MSFTNGP10.phx.gbl...
> Two questions:
> 1) Can an OUTER JOIN, such as a LEFT OUTER JOIN be done on more than two
> tables and, if so, what is the syntax for it ?
> I want to do a LEFT OUTER JOIN so that all rows from my left table are
> selected which meet my where conditions, and where matching rows from
> more than one other table are selected based on a match between my left
> table and the other tables.
> 2) In the above scenario, one of the matches occurs between a column
> value in my non-left table and a column value in another one of my
> non-left tables. How can this also be specified in a LEFT OUTER JOIN
> with more than one table in my LEFT OUTER JOIN syntax ?
> Example:
> Table1: ColumnA, ColumnB, other columns etc.
> Table2: ColumnC, ColumnD
> Table3: ColumnE, ColumnF
> Table4: ColumnG, ColumnH
> I want to join these tables such that all rows and columns from Table1
> are selected matching my where condition. ColumnC is also selected when
> ColumnA matches ColumnD, else ColumnC is null. ColumnE is also selected
> when ColumnA matches ColumnF, else ColumnE is null. Finally ColumnG is
> also selected when Table3's ColumnE matches ColumnH, else ColumnG is null.|||Edward,
Conceptually. each "Join" is a join between only two "Relations", or
"Resultsets". When you have more than two tables in a From Clause, and,
therefore, you have two or more joins. the second "Join" that takes place ca
n
be thought of as a Join between the intermediate resultset created by the
first join, and the third table. So the answer to your question depends on
what order, and what exactly, you wish to Join in this second Join...
Two possibilities exist:
You could Join Tables B to A, using Outer Join syntax, and then Join C to
that resultset, also using Outer Join SyntAX...
From TableA
Left Outer Join Table B On .....
Left Outer Join Table C On ......
Or 2) you might be wishing to Join the COmbined Inner Join of Tables B & C
to Table A. In this case you would be joining B & C FIrst, and then Joining
THAT resultset to TableA using Outer Join Syntax
From TableA
Left Outer Join (Table B Join Table C On ....)
On ....
This approach might be used to get ALL Customers, (even the ones with no
Invoices), plus the data from a Invoices and connected Invoice Details table
s
, but only include invoices that have details...
"Edward Diener" wrote:

> Two questions:
> 1) Can an OUTER JOIN, such as a LEFT OUTER JOIN be done on more than two
> tables and, if so, what is the syntax for it ?
> I want to do a LEFT OUTER JOIN so that all rows from my left table are
> selected which meet my where conditions, and where matching rows from
> more than one other table are selected based on a match between my left
> table and the other tables.
> 2) In the above scenario, one of the matches occurs between a column
> value in my non-left table and a column value in another one of my
> non-left tables. How can this also be specified in a LEFT OUTER JOIN
> with more than one table in my LEFT OUTER JOIN syntax ?
> Example:
> Table1: ColumnA, ColumnB, other columns etc.
> Table2: ColumnC, ColumnD
> Table3: ColumnE, ColumnF
> Table4: ColumnG, ColumnH
> I want to join these tables such that all rows and columns from Table1
> are selected matching my where condition. ColumnC is also selected when
> ColumnA matches ColumnD, else ColumnC is null. ColumnE is also selected
> when ColumnA matches ColumnF, else ColumnE is null. Finally ColumnG is
> also selected when Table3's ColumnE matches ColumnH, else ColumnG is null.
>sql