Not grasping outer joins for ms-sqlserver :: 3 tables to join

[Brian Parker]

I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN working
on a three table query.

Assumptions:
-- I have events in the Event table.
-- Each event CAN have one Transaction, but it's not guaranteed
-- Each transaction, ir present, will have one or more Amount records

This would be the pseudo-query without any special joins:

-----------------------------------------
SELECT
a.Name,
SUM( c.amount ) as Total
FROM
Event a,
Transaction b,
Amounts c
WHERE
a.EventID = b.EventID
AND b.TransID = c.TransID
-----------------------------------------

This is fine if there is a Transaction for the Event. But, if there's
no transaction for an event, no record is pulled of course.

What I need is for a record to come back for each event regardless of
the presence of a Transaction. If there's no transaction, then the
"Total" column should be 0.

How would I get an OUTER JOIN to work on this so that each Event gets a
record?

TIA
-BEP

[Doug]

SELECT
a.Name,
SUM( isnull(c.amount,0) ) as Total
FROM
FROM
Event a left outer join
Transaction b on a.EventID = b.EventID
left outer join
Amounts c on b.TransID = c.TransID

[Brian Parker]

Appreciate it - worked great.
-BEP

Doug wrote:

SELECT
a.Name,
SUM( isnull(c.amount,0) ) as Total
FROM
FROM
Event a left outer join
Transaction b on a.EventID = b.EventID
left outer join
Amounts c on b.TransID = c.TransID

[Hugo Kornelis]

On Wed, 15 Mar 2006 11:58:30 -0600, Brian Parker wrote:

I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN working
on a three table query.

Assumptions:
-- I have events in the Event table.
-- Each event CAN have one Transaction, but it's not guaranteed
-- Each transaction, ir present, will have one or more Amount records

This would be the pseudo-query without any special joins:

-----------------------------------------
SELECT
a.Name,
SUM( c.amount ) as Total
FROM
Event a,
Transaction b,
Amounts c
WHERE
a.EventID = b.EventID
AND b.TransID = c.TransID
-----------------------------------------

This is fine if there is a Transaction for the Event. But, if there's
no transaction for an event, no record is pulled of course.

What I need is for a record to come back for each event regardless of
the presence of a Transaction. If there's no transaction, then the
"Total" column should be 0.

How would I get an OUTER JOIN to work on this so that each Event gets a
record?

Hi Brian,

The query posted by Doug will also return events that do have a
transaction, but without any amounts. Your description says that those
don't exisst - but just in case that you want to make sure that these
are excluded, here are two versions that will really only include
transactions with amounts:

SELECT a.Name, SUM(c.Amount) AS Total
FROM Transaction AS b
INNER JOIN Amounts AS c
ON c.TransID = b.TransID
RIGHT JOIN Event AS a
ON a.EventID = b.EventID

Or, if you prefer to include the tables in your query in the "logical"
order (or if you just prefer LEFT outer joins over RIGHT outer joins):

SELECT a.Name, SUM(c.Amount) AS Total
FROM Event AS a
LEFT JOIN (Transaction AS b
INNER JOIN Amounts AS c
ON c.TransID = b.TransID)
ON a.EventID = b.EventID

Note the order of the join and on clauses. I added parentheses for
clarity, though SQL Server will understand this just as well without the
parentheses.

--
Hugo Kornelis, SQL Server MVP

[Brian Parker]

Hugo Kornelis wrote:

The query posted by Doug will also return events that do have a
transaction, but without any amounts. Your description says that those
don't exisst - but just in case that you want to make sure that these
are excluded, here are two versions that will really only include
transactions with amounts:

Thanks, Hugo. Between all of the examples I'll be able to grasp what's
going on with the join.

Appreciate it!

-BEP