Problem Summing On Joined Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem Summing On Joined Tables

Author

Topic

mmarseil
Starting Member

1 Post

Posted - 2002-01-11 : 18:58:55

Here is the way my data is set up:

TableA:
MYID RandFld
1 A
2 B
3 A
4 B
5 D

TableB:
MYID Amount
2 10
2 20
3 10

TableC:
MYID Amount
1 50
2 20
3 60
4 10

So, this is my query which is summing duplicate records.

Select SUM(TableB.Amount) As Expr1, SUM(TableC.Amount) As Expr2 From (TableB RIGHT JOIN TableA ON (TableB.MYID = TableA.MYID)) LEFT JOIN TableC ON (TableC.MYID = TableA.MYID) Where TableA.RandFld = 'B'

My Result Set Is:
Expr1 Expr2
----- -----
30 50

which is incorrect!!

The True Answer Should Be:
Expr1 Expr2
----- -----
30 30

As you can see, I can't use INNER JOIN because a MYID may be found in Table B, but not in Table C and I need all included. Thanks again!!

Nazim
A custom title

1408 Posts

Posted - 2002-01-12 : 00:37:15

SELECT sum(isnull(AmtB,0)),sum(isnull(amtC,0)) FROM TABLEA A LEFT JOIN
(select myid,sum(Amount) as AmtB from TABLEB
group by myid) b
ON A.MYID=B.MYID
LEFT JOIN
(SELECT MYID, SUM(AMOUNT) AS AMTC FROM TABLEc
GROUP BY MYID) c
ON a.MYID=c.MYID
where a.randfld='B'

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources