wrong query result - 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 2000 Forums

Transact-SQL (2000)

wrong query result

Author

Topic

didin
Starting Member

1 Post

Posted - 2008-05-23 : 09:35:18

Hi all,
I'm trying to joining three tables below

T1
str_no | pos_no | trn_no | grossale | discount
001 | 001 | 00001 | 50 | 5
001 | 001 | 00001 | 30 | 0
001 | 002 | 00002 | 20 | 0
001 | 003 | 00003 | 60 | 10
....

T2
str_no | pos_no | trn_no | grossale | discount
001 | 001 | 00001 | 80 | 5
001 | 002 | 00002 | 20 | 0
001 | 003 | 00003 | 60 | 10
....

T3
str_no | pos_no | trn_no | payment
001 | 001 | 00001 | 45
001 | 001 | 00001 | 30
001 | 002 | 00002 | 20
001 | 003 | 00003 | 60
....

using this query

select T1.str_no,T1.pos_no,sum(T1.grossale-discount) as Netto1,
sum(T2.grossale-discount) as Netto2,
sum(T3.payment) as Payment
from T1
left outer join T2 on (T1.trn_no=T2.trn_no)
left outer join T3 on (T2.trn_no=T3.trn_no)
group by T1.str_no,T1.pos_no
order by T1.str_no,T1.pos_no

I wanna view result of those query :

str_no | pos_no | Netto1 | Netto2 | Payment
001 001 75 75 75
001 002 20 20 20
...

But, the problem is result between Netto1,Netto2 and Payment are different

Please tell me what's wrong

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 10:01:16

[code]DECLARE @t1 TABLE (str_no INT, pos_no INT, trn_no INT, grossale INT, discount INT)

INSERT @t1
SELECT 001 , 001 , 00001 , 50 , 5 UNION ALL
SELECT 001 , 001 , 00001 , 30 , 0 UNION ALL
SELECT 001 , 002 , 00002 , 20 , 0 UNION ALL
SELECT 001 , 003 , 00003 , 60 , 10

DECLARE @t2 TABLE (str_no INT, pos_no INT, trn_no INT, grossale INT, discount INT)

INSERT @t2
SELECT 001 , 001 , 00001 , 80 , 5 UNION ALL
SELECT 001 , 002 , 00002 , 20 , 0 UNION ALL
SELECT 001 , 003 , 00003 , 60 , 10

DECLARE @t3 TABLE (str_no INT, pos_no INT, trn_no INT, payment INT)

INSERT @t3
SELECT 001 , 001 , 00001 , 45 UNION ALL
SELECT 001 , 001 , 00001 , 30 UNION ALL
SELECT 001 , 002 , 00002 , 20 UNION ALL
SELECT 001 , 003 , 00003 , 60

select T1.str_no,
T1.pos_no,
sum(T1.grossale - t1.discount) as Netto1,
T2.Netto2,
T3.payment
from @T1 as t1
left join (
select trn_no,
sum(grossale - discount) AS netto2
from @t2
group by trn_no
) as t2 on T2.trn_no = T1.trn_no
left join (
select trn_no,
sum(payment) AS payment
from @t3
group by trn_no
) AS T3 on T3.trn_no = T2.trn_no
group by T1.str_no,
T1.pos_no,
t2.netto2,
t3.payment
order by T1.str_no,
T1.pos_no[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources