By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table TABLE1 (table1_id int);
create table TABLE2 (table2_id int, table1_id int, earning money);
create table TABLE3 (table3_id int, table1_id int);
insert into TABLE1 values (1);
insert into TABLE2 values (49, 1, 10000); -- Expected sum = 10000
insert into TABLE3 values (991, 1); -- Expected count = 3
insert into TABLE3 values (992, 1);
insert into TABLE3 values (993, 1);
insert into TABLE1 values (2);
insert into TABLE2 values (21, 2, 3000); -- Expected sum = 7000.00
insert into TABLE2 values (22, 2, 4000);
insert into TABLE3 values (201, 2); -- Expected count = 5
insert into TABLE3 values (202, 2);
insert into TABLE3 values (203, 2);
insert into TABLE3 values (204, 2);
insert into TABLE3 values (205, 2);
13 rows affected
-- Inflated results due to
-- multiple independene one-to-many joins
select
T1.table1_id, SUM(T2.earning) AS Earning, COUNT(T3.table1_id) AS T3Count
from
TABLE1 T1
inner join
TABLE2 T2 on T2.table1_id = T1.table1_id
inner join
TABLE3 T3 on T3.table1_id = T1.table1_id
group by
T1.table1_id;
| table1_id | Earning | T3Count |
|---|---|---|
| 1 | 30000.0000 | 3 |
| 2 | 35000.0000 | 10 |
-- Fixed using CROSS APPLY
select
T1.table1_id, CA1.Earning, CA2.T3Count
from
TABLE1 T1
cross apply (
select SUM(earning) AS Earning
from TABLE2 T2
where T2.table1_id = T1.table1_id
) CA1
cross apply (
select COUNT(*) AS T3Count
from TABLE3 T3
where T3.table1_id = T1.table1_id
) CA2
| table1_id | Earning | T3Count |
|---|---|---|
| 1 | 10000.0000 | 3 |
| 2 | 7000.0000 | 5 |
-- Fixed using subqueries
select
T1.table1_id, SS1.Earning, SS2.T3Count
from
TABLE1 T1
inner join (
select T2.table1_id, SUM(earning) AS Earning
from TABLE2 T2
group by T2.table1_id
) SS1 on SS1.table1_id = T1.table1_id
inner join (
select T3.table1_id, COUNT(*) AS T3Count
from TABLE3 T3
group by T3.table1_id
) SS2 on SS2.table1_id = T1.table1_id
| table1_id | Earning | T3Count |
|---|---|---|
| 1 | 10000.0000 | 3 |
| 2 | 7000.0000 | 5 |