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