inner join - 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

inner join

Author

Topic

tdb885
Starting Member

5 Posts

Posted - 2011-01-27 : 07:49:55

i make inner join
select KESIM.orderno, sum(ordet.b1+ordet.b2+ordet.b3+ordet.b4+ordet.b5+ordet.b6+ordet.b7+ordet.b8)as POQ ,
sum (KESIM.b1+KESIM.b2+KESIM.b3+KESIM.b4+KESIM.b5+KESIM.b6+KESIM.b7+KESIM.b8) as cutting,
sum((ordet.b1+ordet.b2+ordet.b3+ordet.b4+ordet.b5+ordet.b6+ordet.b7+ordet.b8)-(KESIM.b1+KESIM.b2+KESIM.b3+KESIM.b4+KESIM.b5+KESIM.b6+KESIM.b7+KESIM.b8)) as [balance]
from kesim
join ordet on ordet.orderno =kesim.orderno
group by KESIM.orderno
order by KESIM.orderno

but the summation was duplicated QTY based on Repetition of order no can't know why SQL do that

Thanks, Best Regards
Mohamed Ibrahim
Application Manager
Tel # 002-02 (24259720)
Fax # 002-02 (24259750)
Mob # 002 (011-2986598)
Email : mhmd.ibrhm@shamsi.com.eg
ALSHAMSI FOR READY MADE GARMENTS(SRMG)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 08:49:07

You mean you hve duplicate orderno's in a table and can't understand why the sql joins on all of them?
What do you expect it to do?

If you answer that then we can probably suggest how you should code it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 09:00:02

Maybe
select KESIM.orderno, ordet.tot as POQ ,
KESIM.tot as cutting,
ordet.tot - KESIM.tot as [balance]
from (select orderno, tot = sum(b1+b2+b3+b4+b5+b6+b7+b8) from kesim group by orderno) kesim
join (select orderno, tot = sum(b1+b2+b3+b4+b5+b6+b7+b8) from ordet group by orderno) ordet
on ordet.orderno =kesim.orderno
group by KESIM.orderno
order by KESIM.orderno

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tdb885
Starting Member

5 Posts

Posted - 2011-01-27 : 10:16:49

Double the quantities beaten in the number of lines

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 10:23:35

Did you see my second post?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tdb885
Starting Member

5 Posts

Posted - 2011-01-27 : 10:54:53

yes but its not correct

tdb885
Starting Member

5 Posts

Posted - 2011-01-27 : 10:56:49

explain to me what do u want because u not see the data and tables

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 11:04:38

try
select KESIM.orderno, sum(ordet.b1+ordet.b2+ordet.b3+ordet.b4+ordet.b5+ordet.b6+ordet.b7+ordet.b8)as POQ ,
sum (KESIM.b1+KESIM.b2+KESIM.b3+KESIM.b4+KESIM.b5+KESIM.b6+KESIM.b7+KESIM.b8) as cutting,
sum((ordet.b1+ordet.b2+ordet.b3+ordet.b4+ordet.b5+ordet.b6+ordet.b7+ordet.b8)-(KESIM.b1+KESIM.b2+KESIM.b3+KESIM.b4+KESIM.b5+KESIM.b6+KESIM.b7+KESIM.b8)) as [balance]
from (select distinct orderno, (b1,b2,b3,b4,b5,b6,b7,b8) from kesim) kesim
join ((select distinct orderno, (b1,b2,b3,b4,b5,b6,b7,b8) from ordet) ordet
on ordet.orderno =kesim.orderno
group by KESIM.orderno
order by KESIM.orderno

Have you any idea where the doubled amounts are coming from?
Try adding a where clause for a single orderno with the proble,
Then remove the group by and do a select * - it should show you where the probelm is.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tdb885
Starting Member

5 Posts

Posted - 2011-01-27 : 11:43:30

my friend let me explain to you i have only own order No. and a lot-of colors and sizes on same order (example 5 rows same order no and different colors and sizes ) when i sum Qty horizontal and vertical on the same order no is multiplied the quantities in the number of repeat order No.

note i am beginner T-SQL

thanks for your help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 11:51:54

The
select orderno, tot = sum(b1+b2+b3+b4+b5+b6+b7+b8) from kesim group by orderno
Should give the total for an order from that table.
Try it for an order
select orderno, tot = sum(b1+b2+b3+b4+b5+b6+b7+b8) from kesim where orderno = xxxx
- do the same for the other table and tell me which one (or both) gives an incorrect vale.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources