Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

How to JOIN Multiple Transactional Tables in SQL

A common difficulty beginning SQL programmers encounter is joining two or more transactional tables all in one SELECT statement.  Missing data, duplicates, time-out errors, and other unexpected results often arise from trying to directly write JOINS between two transaction tables.

Consider a simple schema with the following tables (primary key columns in bold):

Budget:  Year, Month, Account, Dept, Amount

Commitments: CommitmentID (Identity), Year, Month, Account, Dept, Amount

Actuals: ActualID (Identity), Year, Month, Account, Dept, Amount

That is basically what I've inherited in the latest application I've been tasked to clean up. Data comes from several sources, so I suppose that explains why things are in different tables.  The basic idea is that your budget is set by Account/Dept/Year/Month, and you need to write reports that let you see Budget, Actual and Commitments amounts side by side with a few calculations as well.

After looking at the existing hundreds of lines of temp tables, cursors, update statements and other strange ways people have of writing simple SELECTs, I am reminded that it is not always obvious how to join several transactional tables into 1 SELECT statement.

So, how do you get Budgets, Commitments and Actuals all together?  

The answer is: not by JOINing them together ... but by UNIONing them together.  By layering the tables on top of one another, and spreading out the Amounts from each table into its own column, and then grouping it all together and SUMing up each amount column, we will have the results we need.   Essentially, we are using the UNION ALL operator to create 1 big transaction table with all of our data, and then simply aggregating the results.  (Remember that we use UNION ALL and not just UNION since it is more efficient).

So, if we wanted to see the numbers for a particular @Year, with totals for each Month, Account and Department, we could write:

select
   Year, Month, Account, Dept,
   sum(Budget) as Budget,
   sum(Commitment) as Commitment,
   sum(Actual) as Actual
from
(
 select
   Year, Month, Account, Dept, Amount as Budget, 0 as Commitment, 0 as Actual
 from
   Budget

 union all

 select
   Year, Month, Account, Dept, 0 as Budget, Amount as Commitment, 0 as actual
 from
   Commitments

 union all

 select
   Year, Month, Account, Dept, 0 as Budget, 0 as Commitment, Amount as actual
 from
   Actuals
)
  tmp
where
  Year = @Year
group by
  Year, Month, Account, Dept

And .. that's it.  We're done!

We don't need to worry about months where there is a budget but no actual, or an actual but no budget, which becomes a common problem with JOINS.  We don't need to use an ugly FULL OUTER JOIN and wrap up every single column in COALESCE() functions.  We don't need long derived table expressions.  And we don't need to worry about multiple Commitments or Actuals all in the same month causing duplicates and affecting our Budgets.  It's all taken care of for us.

Of course, you would wrap this SQL statement in a derived table, join it to the Accounts and Department tables, and calculate a few more columns (variance, percentages, etc) to complete our report.   We also could remove the Month grouping from our SELECT and calculate only one value per Year/Account/Dept, with Year-to-Date and Month-To-Date columns very easily. 

So, don't forget about UNION ALL ... it's very powerful and really a great way to easily summarize and compare two or more unrelated transactional tables.

Well, I better get back to the cursor busting ....

see also:

Print | posted on Monday, June 19, 2006 3:34 PM | Filed Under [ T-SQL Techniques Joins/Relations GROUP BY ]

Feedback

Gravatar

# re: Joining Multiple Transactional Tables


"I understand that this is your attempt at humor, but it's just not funny, Jeff. You suck. Don't quit your day job!"

Wow, makes sense everytime!

Thanks Jeff for this words of wisdom, I'm starting to post them on every forum I read.

Mike Rod.
6/19/2006 6:33 PM | Mike Rod
Gravatar

# re: Joining Multiple Transactional Tables

<topic>Oh!, this is soo bad. Why don't you resume next on your errors?</topic>

Anyways thanks for a good example in the use of union all, they seem hard to find nowadays.
6/20/2006 5:07 PM | Anton
Gravatar

# re: Joining Multiple Transactional Tables

Hi,

Do you have a example of Budget: (Year, Month, Account, Dept) (PK), Amount

Commitments: CommitmentID (Identity, PK), Year, Month, Account, Dept, Amount

Actuals: ActualID (Identity, PK), Year, Month, Account, Dept, Amount
I am a finacial controller en looking for a good example in access. Ik hope that you can help me.

Regards,

Geert Pantein
(from Belgium)
email:pantein@hotmail.com
7/6/2006 3:33 PM | Geert
Gravatar

# re: Joining Multiple Transactional Tables

omgomgomg, ok, ok, here I go:

Excuse me, mr. No0b but it is a proven fact that Access has no XML support, plus everyone using Access should be soaked on honey and bitten by fire ants. It includes VB!! N00b-oron

---------------------------------------------------

Jeff, Jeff, how did I do? =D
7/10/2006 10:39 AM | Mike Rod
Gravatar

# re: Joining Multiple Transactional Tables

Hi,

Good Example with Union all and wonderful Explanation. I solved my problem.In fact i am looking for an example for union all i found here.

Thank You

rgds
Devaraya
7/13/2006 1:33 AM | Devaraya
Gravatar

# re: How to JOIN Two Derived Tables

Pls send me the sample code to join two derived table

example
select customedID, customerName from Customer

and

select CustomerAddress, CustomerContact from CusContact

derived table of the first query and derived table of the second query in to a single derived table
7/2/2007 8:38 AM | Sudharsan
Gravatar

# re: How to JOIN two tables with sum

Hi,

It is nice to interact with u all .i have a little problem with agregate function (sum) actually i am new to sql server
i want a query.i want to add two tables and sum them taking one column from each table. and it should display
sum of two column.
8/21/2007 3:28 AM | imtiaz
Gravatar

# re: How to JOIN Multiple Transactional Tables in SQL

How does this technique work with bit columns? I can't find an agggregate function that works with bit columns.
2/25/2009 9:34 AM | anon
Gravatar

# re: How to JOIN Multiple Transactional Tables in SQL

anon:

http://www.google.com/search?q=sql+server+aggregate+bit+columns
2/25/2009 9:45 AM | Jeff S
Gravatar

# re: How to JOIN Multiple Transactional Tables in SQL

Thanks a lot!!
I am getting into the SQ sintax world and you tought something really useful!!
6/1/2009 4:33 PM | Miguel
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET