|
|
Posted by Hugo Kornelis on 10/01/71 11:42
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
Navigation:
[Reply to this message]
|