join problem ... please help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

join problem ... please help

Author

Topic

bonhasgone
Starting Member

1 Post

Posted - 2008-12-02 : 17:31:14

hi ,

if i use the following query

select tradername, isnull(sum(btty.debit),0)- isnull(sum(btty.credit),0)
as opbalance
from dbo.traders
left outer join dbo.btty
on traders.traderid = btty.traderid
group by tradername

i get the result as this
tradername opbalance
harish 0.00
prakash 225.00
rag 0.00
vedu 0.00

if i use this query i get

select tradername,
isnull(sum(balancetable.debit),0)- isnull(sum(balancetable.credit),0)
as closingbalance
from dbo.traders
left outer join dbo.balancetable
on traders.traderid = balancetable.traderid
group by tradername

tradername closingbalance
harish 0.00
prakash 1025.00
rag 0.00
vedu 0.00

but when i try to join these both queries like

select tradername, isnull(sum(btty.debit),0)- isnull(sum(btty.credit),0)
as opbalance,
isnull(sum(balancetable.debit),0)- isnull(sum(balancetable.credit),0)
as closingbalance
from dbo.traders
left outer join dbo.btty
on traders.traderid = btty.traderid
left outer join dbo.balancetable
on traders.traderid = balancetable.traderid
group by tradername

i get this
tradername opbalance closingbalance
harish 0.00 0.00
prakash 900.00 1025.00
rag 0.00 0.00
vedu 0.00 0.00

i have no idea why the opbalance for prakash is showing as 900
it should be 225 . what am i doing wrong ..

Please help

thanks and regards
bonhasgone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 00:00:49

use the below
your approach might skew if there's a many to one relation between traders and any other table

SELECT t1.tradername,
t1.opbalance,t2.closingbalance
FROM (
select tradername, isnull(sum(btty.debit),0)- isnull(sum(btty.credit),0) as opbalance
from dbo.traders 
left outer join dbo.btty
on traders.traderid = btty.traderid
group by tradername 
)t1
inner join (
select tradername, 
isnull(sum(balancetable.debit),0)- isnull(sum(balancetable.credit),0)
as closingbalance
from dbo.traders 
left outer join dbo.balancetable
on traders.traderid = balancetable.traderid
group by tradername 
)t2
on t2.tradername=t1.tradername

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources