Works like a charm Boris. Thank you :o)


>>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.