Query for joining data from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query for joining data from two tables

Author

Topic

Neven1986
Starting Member

6 Posts

Posted - 2010-04-11 : 11:10:43

Hello I'm Neven,
i'm very new to SQL programming. I have problem that i'm trying to solve for days. So please help me...

I have to tables:

.......INCOME...................OUTCOME
ID..Amount..Date..............ID..Amount..Date
1....100...10.04.2010.........1...200...10.4.2010
2....200...10.04.2010.........2...150...11.4.2010
3....300...11.04.2010.........3...300...11.4.2010

Is there any way to write query which will SUM both tables AMOUNTH based on DATE to get the following output:

STATUS
ID....Amount....Date
1......100......10.04.2010
2......-150.....11.04.2010

STATUS.Amount = INCOME.Amount - OUTCOME.Amount

Thank you in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-11 : 11:50:56

[code]
SELECT ID,
SUM(CASE WHEN Cat='I' THEN Amount ELSE -1 * Amount END) AS Amount,
Date
FROM
(SELECT ID,Amount,Date,'I' AS Cat
FROM INCOME
UNION ALL
SELECT ID,Amount,Date,'O'
FROM OUTCOME
)t
GROUP BY ID,Date
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-11 : 12:19:09

STATUS.Amount = INCOME.Amount - OUTCOME.Amount

That doesn't actually fit the data you specified. That formula would give you:

STATUS
ID....Amount....Date
1......-100......10.04.2010
2......50.....11.04.2010

Anyhow, if you just want to subtract one column for another, then you don't need to SUM them. This will work, assuming that ID is a primary key or unique within each table.

SELECT i.ID, i.Amount - o.Amount AS Status, i.Date
FROM INCOME i
INNER JOIN OUTCOME o
	ON i.ID = o.ID
WHERE i.Amount != o.Amount

The WHERE clause is there to filter out record ID 3, which you omitted from your sample output. If you want to include records where INCOME = OUTCOME, then simply remove the where clause.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Neven1986
Starting Member

6 Posts

Posted - 2010-04-12 : 09:34:46

Thank you guys for reply. I will try your suggestions. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources