Result changes with added table

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)

Result changes with added table

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-01-24 : 16:08:02

I'm curious why my result would change if I added an extra table in my query. Here's the example:

This is the right output:

SELECT DISTINCT PRODCUT_ID, SUM(SALE_AMOUNT) AS SALE
FROM PRODUCT_ORDER INNER JOIN
CUST_ORDER ON PRODUCT_ORDER.ORDER_ID = CUST_ORDER.ORDER_ID
WHERE (PRODUCT_ORDER.SHIP_DATE BETWEEN '12/31/2003' AND '12/31/2004')
AND (PRODUCT_ORDER.PRODUCT_ID IS NOT NUULL)
GROUP BY PRODUCT_ORDER.PRODUCT_ID
ORDER BY PRODUCT_ORDER.PRODUCT_ID

Output:

apple 234.00
orange 254.00
banana 786.34

When I add an extra table it all changes:

SELECT DISTINCT PRODCUT_ID, SUM(SALE_AMOUNT) AS SALE
FROM PRODUCT_ORDER INNER JOIN
CUST_ORDER ON PRODUCT_ORDER.ORDER_ID = CUST_ORDER.ORDER_ID
INNER JOIN
INV_TRANS ON PRODUCT_ORDER.ORDER_ID = INV_TRANS.ORDER_ID
WHERE (PRODUCT_ORDER.SHIP_DATE BETWEEN '12/31/2003' AND '12/31/2004')
AND (PRODUCT_ORDER.PRODUCT_ID IS NOT NUULL)
GROUP BY PRODUCT_ORDER.PRODUCT_ID
ORDER BY PRODUCT_ORDER.PRODUCT_ID

Output:

apple 989.00
orange 1002.00
banana 2032.00

The product ID is right, but the sale amount seems to be bigger. What do I need to do even though all I did was add an extra table. I need this table to work on something on later so that's why it's added in there. BTW Right and Left outer join does no difference.

Please help.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-01-24 : 16:11:52

I'm curious if my join table from the product_order.order_id to cust_order.order_id might've alter the sale amount.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-01-24 : 16:11:52

I'm curious if my join table from the product_order.order_id to cust_order.order_id might've alter the sale amount.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 16:13:51

Run this:

SELECT SALE_AMOUNT FROM PRODUCT_ORDER WHERE PRODUCT_ID = 'apple'
SELECT * FROM INV_TRANS i INNER JOIN PRODUCT_ORDER p ON i.ORDER_ID = i.ORDER_ID WHERE p.PRODUCT_ID = 'apple'

Post the results here.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-24 : 16:14:51

ON PRODUCT_ORDER.ORDER_ID = INV_TRANS.ORDER_ID
is the problem.
It's got multiple recs for the same INV_TRANS.ORDER_ID so the SALE_AMOUNT is being included multple times in the sum.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 16:15:05

quote:
Originally posted by chriskhan2000

I'm curious if my join table from the product_order.order_id to cust_order.order_id might've alter the sale amount.

Yes it did. We'll need to see the data. Please run the queries from my last post and post the results.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-01-24 : 16:41:31

Here's the result.

SELECT SALE_AMOUNT FROM PRODUCT_ORDER WHERE PRODUCT_ID = 'apple'

output:
123.00
232.00
154.00
234.00

total: 743

SELECT * FROM INV_TRANS i INNER JOIN PRODUCT_ORDER p ON i.ORDER_ID = i.ORDER_ID WHERE p.PRODUCT_ID = 'apple'

output:

123.00
232.00
154.00
234.00
547.23
321.09
634.21
123.00

total: 2368

So it is a lot more with the second query. Is there a way I can still do what I do but not have all those numbers added?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 16:56:34

You are going to need to exclude the other rows. Are some of these transactions voided for example?

We'd need to see your table structure of both tables though to help out.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources