By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Mar 15 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Mar 15 '06 #2

P: n/a
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

Mar 15 '06 #3

P: n/a
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
Mar 15 '06 #4

P: n/a
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
Mar 15 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.