>Boris,
>
>If Tracy was clear that each table could have multiple IDs, then your code won't work. You'll get double-counting.
>
>You can't do a left join that way, because of the aggregation against multiple tables. Try the following and look at the results. (I simply populated 2 tables and then ran your query).
>
>

>declare @Table1 TABLE (ID int, Owed decimal(14,2), Paid decimal(14,2))
>declare @Table2 TABLE (ID int, TPaid decimal(14,2))
>
>
>insert into @Table1 VALUES (1, 100, 80)
>insert into @Table1 VALUES (1, 200, 180)
>
>insert into @Table1 VALUES (2, 300, 280)
>insert into @Table1 VALUES (2, 400, 380)
>
>insert into @Table1 VALUES (3, 500, 480)
>insert into @Table1 VALUES (3, 600, 580)
>
>
>
>insert into @Table2 VALUES (1, 5)
>insert into @Table2 VALUES (1, 10)
>
>insert into @Table2 VALUES (2, 15)
>insert into @Table2 VALUES (2, 20)
>
>insert into @Table2 VALUES (3, 25)
>insert into @Table2 VALUES (3, 30)
>
>
>select table1.id, sum(owed) as owed, sum(paid) as paid, sum(isnull(table2.tpaid,0)) as tapid
>from @Table1 table1
>left join @Table2 table2 on table1.id = table2.id
>group by table1.id
>

>
>Sergey's reply will produce the correct results - but a correlated subquery is seldom the optimal solution, and there are some DBAs who would make a developer rewrite such a query. I have a follow-up reply to Sergey,but want to wait for an answer to the question I asked Tracy.



O! Sh.........!!!!!!!!!!!
!!!!!!

declare @Table1 TABLE (ID int, Owed decimal(14,2), Paid decimal(14,2))
declare @Table2 TABLE (ID int, TPaid decimal(14,2))


insert into @Table1 VALUES (1, 100, 80)
insert into @Table1 VALUES (1, 200, 180)

insert into @Table1 VALUES (2, 300, 280)
insert into @Table1 VALUES (2, 400, 380)

insert into @Table1 VALUES (3, 500, 480)
insert into @Table1 VALUES (3, 600, 580)



insert into @Table2 VALUES (1, 5)
insert into @Table2 VALUES (1, 10)

insert into @Table2 VALUES (2, 15)
insert into @Table2 VALUES (2, 20)

insert into @Table2 VALUES (3, 25)
insert into @Table2 VALUES (3, 30)


select table1.id, table1.owed, table1.paid, isnull(table2.tpaid,0) as tapid
from (SELECT Id, sum(owed) as owed, sum(paid) as paid
             FROM @Table1
             group by id) Table1
LEFT JOIN(SELECT Id, sum(tpaid) as tpaid
             FROM @Table2
             group by id) Table2
ON table1.id = table2.id

I hate correlated subqueries. They are so slow.