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.
| Author |
Topic |
|
renu
Starting Member
47 Posts |
 Posted - 2007-12-13 : 01:22:41
|
| There are two tables.TABLE A: c1 , amount1table B:c2 ,amount2now i ahve to fine the difference between amount1 and amount2 for allcorresponding c1 and c2 column,i used full outer join,i am getting the duplicated values, so any other method?(derived query) |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-12-13 : 01:24:37
|
What is the primary key of Table A & B ?Can you show us your query ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
renu
Starting Member
47 Posts |
Posted - 2007-12-13 : 01:35:24
|
| select sum(isnull(amount1,0)-isnull(amount2,0)) as balancefrom ( select distinct r1.c1,r1.amount1,r2.c2,r2.amount2 from a as r1 full outer join (select r2.c2,r2.amount2 from b as r2)c on a.c1=c.c2) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-12-13 : 01:37:36
|
can you also show us what do you mean by duplicated values ? Please post some sample data of such scenario
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-12-13 : 01:53:02
|
| Also tryselect c1,sum(amount1) - sum(amount2) as diff from(Select c1, amount1,0 as amount2 from table_aunion allSelect c2, 0,amount2 from table_b) as tMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Derived Query