473,289 Members | 1,917 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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

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
4 1557
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Joshua Goodstein | last post by:
Select d.* from ( select agent_addr, max(date_time) from eventlog where priority = 1 group by agent_addr) d, (select agent_addr, max(date_time) from eventlog where priority = 6 group by...
7
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
1
by: Frank via DBMonster.com | last post by:
Hello, My ODBC driver from DB2 connect does not allow outer joins. Is this normal? Does anyone know how to configure it so it allows outer joins? Greetings, Frank
1
by: Matthi | last post by:
How can I perform in-memory sql joins on dataset's, and have the result in (perhaps) a DataTable? Thanks
0
by: daniel.stahr | last post by:
Hi, I figured out that MQT's can not perform outer joins if you choose refresh immediate. Does anybody know if this feature is planned for any future fixpacks or versions? If not, any ideas for...
1
by: fundakid | last post by:
I do not remember where I saw it first , but here is a recreation of the diagram that IMHO is the best explanation of the different types of joins. ...
2
by: Michael Scott | last post by:
This is driving me nuts. Can anyone help? I have a program which searches for and analyses Access databases across a corporate network. At one point in the code I am looping quickly through all...
9
by: Matthias Buelow | last post by:
Hi folks, I've got something like: class Outer { int f(); friend class Inner; class Inner { int g() {
10
by: Troels Arvin | last post by:
Hello, While looking into a particular SQL statement which seemed to put the database under pressure, I found the following construct which is new to me: SELECT ... FROM ... OUTER UNION...
36
by: TC | last post by:
I've used Access for many years. Several times, I've encountered a bug which I refer to as the "Vanishing Joins" bug. When it happens, joins vanish randomly from queries. More specifically, all...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.