CODEWITHSUNDEEP
sqlsql-server
Revokez
Revokez

Reputation: 323

Left Joining Table with Sum - Causing Issues

I'm trying to Left Join Multiple tables with two sum columns that both join on a mid step table.

The tables look like:

Table1

ID  Value1
1   3
2   2
3   3

Table2

ID  Value1
1   5
2   2
3   2
4   1

Jointable

ID
1
2
3
4
5
6

I'm trying to output:

Table1Value1SUM Table2Value1Sum
8 | 10

With the SQL:

SELECT SUM(Table1.Value1) Table1Value1SUM,SUM(Table2.Value1) Table2Value1Sum From Table1
Left Join JoinTable
On JoinTable.ID = Table1.ID
Left Join Table2
On Table2.ID = Table1.ID

I'm getting these results:

Table1Value1SUM Table2Value1Sum
8 | 9

Upvotes: 2

Views: 60

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

What has jointable to to with this? You want the sums of table1 and table2, hence:

select
  (select sum(value1) from table1) as table1sum,
  (select sum(value1) from table2) as table2sum

or

select t1.table1sum, t2.table2sum
from
  (select sum(value1) as table1sum from table1) t1
cross join
  (select sum(value1) as table2sum from table2) t2;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Left Join the Table2 with JoinTable instead of Table1

SELECT SUM(Table1.Value1) Table1Value1SUM,SUM(Table2.Value1) Table2Value1Sum From Table1
Left Join JoinTable
On JoinTable.ID = Table1.ID
Left Join Table2
On JoinTable.ID = Table2.ID

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

jointable has to be left joined upon with the other 2 tables.

SELECT SUM(Table1.Value1) Table1Value1SUM,SUM(Table2.Value1) Table2Value1Sum 
From JoinTable 
Left Join Table1 On JoinTable.ID = Table1.ID
Left Join Table2 On JoinTable.ID = Table2.ID

Upvotes: 3

Related Questions