joining multiple tables

[rtantek]

i have 3 tables: accounts, bank1, bank2. i need to sum the field [mybalance] from both bank1 and bank2 where acctnum from bank1 and bank2 exist in accounts. all acctnum in bank1 are in accounts; all of acctnum in bank2 are in accounts; none of acctnum in bank1 are in bank2 and vice versa.

desired result:
accounts.acctnum, sum(bank1.mybalance + bank2.mybalance)

can this be done without the use of views.

Please help.

 

[r937]

[tt]select accounts.acctnum
, sum(mybalance)
from accounts
inner
join (
select acctnum
, mybalance
from bank1
union all
select acctnum
, mybalance
from bank2
) as u
on accounts.acctnum = u.acctnum
group
by accounts.acctnum[/tt]

rudy

http://r937.com/