Problem with 2 tables query - 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)

Problem with 2 tables query

Author

Topic

Joe.B
Starting Member

5 Posts

Posted - 2002-08-27 : 13:42:14

Hello there,
i got stuck here with this query. I joined 2 tables and do the sum for total amount, but the query results in duplicated rows and the total amount also incorrect. I wrote a query for each of the tables and it worked fine, but the problem appears after i joined the tables. Can anyone find out what's wrong with my query? Thanks in advanced.

here's the sample of the tables:

CustomerOrders
OrdID Item  OrdDate    ProdID  Amount ShipmentDate
S010   1    2000/1/17  21ZXD0  1000   2000/1/26
S010   2    2000/1/17  31ZXD3  1000   2000/1/27
S010   3    2000/1/17  91ZXD4  1000   2000/2/12 
S010   4    2000/1/17  51ZX3S  1000   2000/2/14

OrdersOut
OutID  Item ProdID  Amount  ShipmentDate  OrdID
AB123   1   21ZXD0   500      2000/1/25   S010
AB124   1   21ZXD0   500      2000/1/25   S010
AB125   2   31ZXD3   500      2000/1/27   S010
AB126   2   31ZXD3   500      2000/2/2    S010
AB127   3   91ZXD4   1000     2000/2/12   S010
AB128   4   51ZX3S   1000     2000/2/14   S010

SELECT 	customerOrders.OrdID, customerOrders.OrdDate, SUM(customerOrders.Amount) AS TotalOrder,
	SUM(OrdersOut.Amount) AS TotalOut, SUM(customerOrders.Amount) - SUM(OrdersOut.Amount) AS Diff

FROM    customerOrders LEFT OUTER JOIN
          OrdersOut ON customerOrders.OrdID = OrdersOut.OrdID

WHERE  (customerOrders.Customer = 'Joe') AND (customerOrders.OrdID IN
       (SELECT   OrdID
        FROM     CustomerOrders
        WHERE    ordDate < '2000/1/31')) AND (OrdersOut.OrdID IN
       (SELECT   OrdID
        FROM     OrdersOut
        WHERE    ShipmentDate < '2000/1/31')) AND (customerOrders.OrdID IN
       (SELECT   OrdID
        FROM     CustomerOrders
        WHERE    OrdDate BETWEEN '2000/1/1' AND '2000/1/31'))

GROUP BY  customerOrders.OrdID, OrdersOut.Amount, customerOrders.OrdDate



the result i expect is:

OrdID  OrdDate  TotalOrder TotalOut  Diff
S010  2000/1/17    2000      1500    500

but my result is:
OrdID  OrdDate  TotalOrder TotalOut  Diff
S010  2000/1/17	  12000	    6000     6000
S010  2000/1/17	  6000	    6000        0

chadmat
The Chadinator

1974 Posts

Posted - 2002-08-27 : 15:42:14

Well,

I'm not sure your tables are normalized. You are creating multiple joins between the 2 tables.

so the first row in customerorders is joined with the first 3 rows in OrdersOut. The same is true for the second row in CustomerOrders. That is where 6000 comes from.

It is a cartesian product (Of sorts)

-Chad

j9988
Starting Member

2 Posts

Posted - 2002-08-28 : 02:47:32

--YOU COUND TRY:
SELECT customerOrders.OrdID, customerOrders.OrdDate, SUM(customerOrders.Amount) AS TotalOrder,
SUM(OrdersOut.Amount) AS TotalOut, SUM(customerOrders.Amount) - SUM(OrdersOut.Amount) AS Diff

FROM customerOrders LEFT JOIN
OrdersOut ON customerOrders.OrdID = OrdersOut.OrdID

WHERE
customerOrders.Customer = 'Joe' and
OrdDate BETWEEN '2000/1/1' AND '2000/1/31' and
ShipmentDate < '2000/1/31'
GROUP BY customerOrders.OrdID,customerOrders.OrdDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources