Posted by Brian Parker on 10/01/10 11:42

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

[Back to original message]