Outer Join Problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Outer Join Problem

Author

Topic

Les.61
Starting Member

49 Posts

Posted - 2009-12-15 : 21:11:52

I an trying to report on ALL accounts and then total of transactions from a particular date. If there are no transactions since that date it should return o.oo or NULL

My code is

use reporting
go
select a.balance, sum (transamount)AS TotalToDate
from dbo.AcctransCrDr as A
right outer join dbo.TranAccount As t
on a.id = t.id
--where transprocessdate >= '2009-12-01 00:00:00.000'
group by A.Id, a.balance
ORDER BY TotalToDate

It works fine and returns 7,300 lines however when I uncomment the 'where' line it only returns any accounts with transactions since 1 Dec 2009. I thought using a right outer join would list all from the right hand table (ie TranAccount) and then the sum of balances from the AcctransCrDr if appliable otherwise NULL.

Where am I going wrong?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 01:54:20

Instead of where use and

Madhivanan

Failing to plan is Planning to fail

Les.61
Starting Member

49 Posts

Posted - 2009-12-16 : 18:39:10

Still only get the accounts that have had transactions and not all the accounts.

Les

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-17 : 01:56:58

Try this

use reporting
go
select a.balance, sum (transamount)AS TotalToDate
from dbo.TranAccount As t
left outer join dbo.AcctransCrDr as A
on a.id = t.id
and transprocessdate >= '2009-12-01 00:00:00.000'
group by A.Id, a.balance
ORDER BY TotalToDate

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources