How to combine two tables in a query

Question

How to combine two tables in a query

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

pieterdv (6/29/2010)
Steve Cullen (6/29/2010)
The confusing part to me is you say records from one will not be in the other table. If that is so, you have no way to join the two tables and you would need a union.
If however, you have something in common, such as Org, Period and Account, you would perform a join, probably a full outer join.
If I was unclear, apologies. What I meant to say is that the Budget table does not have entries for all the actual accounts, since it looks into the future. The actuals does not have expenditure for all the budgeted accounts becuase there is deviation from the budget. This does not mean that the Org, Period and accounts does not have anything in common, they are the same data types.
Having said that, the following query do combine the two tables. However, I still do not know how to seperate the actual amount from the budget amount since the query return the budgetAmount below the Amount column. I need the Actual Amount in a seperate column from the Budget Amount.
SELECT GLSummary.Org, GLSummary.Period, GLSummary.Account, GLSummary.Amount
FROM SSI.dbo.GLSummary GLSummary
WHERE (GLSummary.Org='RSA/JNB/T00') AND (GLSummary.Period>=201001)
UNION
SELECT IntGLBudgets.Org, IntGLBudgets.period, IntGLBudgets.account, IntGLBudgets.budgetAmount
FROM SSI.dbo.IntGLBudgets IntGLBudgets
WHERE (IntGLBudgets.Org='RSA/JNB/T00') AND (IntGLBudgets.period>=201001)

How does this work?

SELECT GLSummary.Org, GLSummary.Period, GLSummary.Account, GLSummary.Amount, BudgetAmount = convert(money, NULL)

FROM SSI.dbo.GLSummary GLSummary

WHERE (GLSummary.Org='RSA/JNB/T00') AND (GLSummary.Period>=201001)

UNION

SELECT IntGLBudgets.Org, IntGLBudgets.period, IntGLBudgets.account, convert(money, NULL), IntGLBudgets.budgetAmount

FROM SSI.dbo.IntGLBudgets IntGLBudgets

WHERE (IntGLBudgets.Org='RSA/JNB/T00') AND (IntGLBudgets.period>=201001)

Edit: If this doesn't work, let's go back and start over at step one. Which is to read the first llink in my signature. Step 2 is to post some DDL (CREATE TABLE statement) for the tables that you are quering. Step 3 is to post some DML (INSERT statements) to put some test data into those tables. And step 4 is to show us, based upon the data in Step 3, what your desired output is to be.

This will help us all to understand exactly what you are looking for, and will make it easier for people to help you. So, help us help you by providing this information for us.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Look at my example. You'd need to do this:

SELECT GLSummary.Org

, GLSummary.Period

, GLSummary.Account

, 0 'Budget Amount'

, GLSummary.Amount 'Actual Amount'

FROM SSI.dbo.GLSummary GLSummary

WHERE (GLSummary.Org='RSA/JNB/T00')

AND (GLSummary.Period>=201001)

UNION

SELECT IntGLBudgets.Org

, IntGLBudgets.period

, IntGLBudgets.account

, IntGLBudgets.budgetAmount 'Budget Amount'

, 0 'Actual Amount'

FROM SSI.dbo.IntGLBudgets IntGLBudgets

WHERE (IntGLBudgets.Org='RSA/JNB/T00')

AND (IntGLBudgets.period>=201001)

pieterdv SSC Journeyman Points: 75 More actions · Answer 3

Steve, Wayne

Thank you!

Both of your statements give the desired results. Thanks for extracting the teeth! It appears to have been painfull. Thanks for the patience!

Best regards

Pieter