Multiple Joined Tables that sum

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)

Multiple Joined Tables that sum

Author

Topic

cardgunner

326 Posts

Posted - 2007-05-18 : 14:42:16

Newbie that is learning.

I have four Tables
master
part
labor
misc
And I'm trying to sum the field where the costs are. This is a one to many relationship between master and the others.

select 	master.t_orno,
	isnull(convert(decimal(9,2),sum(part.t_inam)),0)as 'PARTS',
	isnull(convert(decimal(9,2),sum(labor.t_inam)),0)as 'LABOR',
	isnull(convert(decimal(9,2),sum(misc.t_inam)),0)as 'MISC'
from ttssoc200100 as master
 join ttssoc220100 as part
on master.t_orno=part.t_orno
 join ttssoc230100 as labor
on master.t_orno=labor.t_orno
 join ttssoc240100 as misc
on master.t_orno=misc.t_orno 
where master.t_osta='15'
group by master.t_orno
order by master.t_orno

What happening is it's taking the results and multiple them by the number of records in each table. ?!?

Where it should be $359 it $53900.

I have tried left/right/inner/outer/cross joins and they have all failed.

I tried to understand a loop but got lost.

Any help?

Card Gunner

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-18 : 14:52:35

post some sample data from each of the tables for master.t_osta='15'

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cardgunner

326 Posts

Posted - 2007-05-18 : 15:08:35

[code]Print 'LABOR'
select t_orno as 'W/O',
convert (Decimal(9,2),t_inam )as 'COST'
from ttssoc230100
where t_orno='E60000009'
Print 'Part'
select t_orno as 'W/O',
convert (Decimal(9,2),t_inam )as 'COST'
from ttssoc220100
where t_orno='E60000009'
Print 'MISC'
select t_orno as 'W/O',
convert (Decimal(9,2),t_inam )as 'COST'
from ttssoc240100
where t_orno='E60000009'

LABOR
W/O COST
--------- -----------
E60000009 150.00
E60000009 90.00
E60000009 42.00
E60000009 254.10
E60000009 162.00
E60000009 102.00
E60000009 48.00
E60000009 78.00
E60000009 346.50
E60000009 215.60
E60000009 18.00
E60000009 42.00
E60000009 18.00
E60000009 150.00
E60000009 90.00
E60000009 120.00
E60000009 30.00
E60000009 120.00
E60000009 372.00
E60000009 150.00
E60000009 198.00

(21 row(s) affected)

Part
W/O COST
--------- -----------
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 3.44
E60000009 6.60
E60000009 6.50
E60000009 .68
E60000009 3.00
E60000009 .86
E60000009 1.31
E60000009 4.35
E60000009 3.08
E60000009 2.68
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 134.96
E60000009 135.44
E60000009 13.56
E60000009 12.88
E60000009 4.01
E60000009 25.76
E60000009 12.88
E60000009 9.18

(35 row(s) affected)

MISC
W/O COST
--------- -----------
E60000009 40.81
E60000009 21.60
E60000009 5.10
E60000009 .00
E60000009 .00
E60000009 .00
E60000009 36.00

(7 row(s) affected)

[/code]

Card Gunner

Sorry it's a little long

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-18 : 17:33:23

If you see the code:

...
from ttssoc200100 as master
 join ttssoc220100 as part
...

You are joining the table aliased as master with the same table aliased as past. If the master has 35 records you are getting 35 * 35 records which will blow up the totals. Check if the query is correct logically.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cardgunner

326 Posts

Posted - 2007-05-21 : 14:25:55

ttssoc200100 and ttssoc220100 are different tables.

Maybe I'm not understanding your explanation? Can we revisit?

Card Gunner

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-21 : 16:08:29

See if this helps you:

http://weblogs.sqlteam.com/jeffs/archive/2006/06/19/10270.aspx

The technique shown there should work perfectly for you.

Remember that you cannot directly join a whole bunch of transactional tables that have no relation all in 1 SQL statement ... you will get lots of duplicate rows and cross joins and all other kinds of things happening. You need to union them all together or aggregate them all separately.

- Jeff
http://weblogs.sqlteam.com/JeffS

cardgunner

326 Posts

Posted - 2007-05-21 : 16:58:07

Well I'm working on this UNION ALL. But to clarify there is a relation on t_orno.

Card Gunner

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-21 : 18:20:55

quote:
Originally posted by cardgunner

Well I'm working on this UNION ALL. But to clarify there is a relation on t_orno.

Card Gunner

That is correct, those transactional tables have a common relation to t_orno -- but only to the table where t_orno is defined. Those transactional tables don't have direct relations to *each other*, which is why you cannot join them directly. If they only relate by t_orno, you must first summarize them separately by the T-orno column and *then* you can join them together, or UNION them all together (which is easier and more efficient).

- Jeff
http://weblogs.sqlteam.com/JeffS

cardgunner

326 Posts

Posted - 2007-05-22 : 09:29:39

Wow, this is working out extremely well, I'm grinning from ear to ear

I just need to work in the other fields and my where clause and check the number of records. This is amazing. Thanks for the help.

Card Gunner

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-22 : 11:53:48

Any time, glad it worked! It's quite easy when you know the technique. UNION ALL is awesome for joining transactional tables!

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources