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

Outer Join Challenge

I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>

Outer Join Challenge

I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>sql