Yeh another annoying 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

SQL Server 2000 Forums

Transact-SQL (2000)

Yeh another annoying join

Author

Topic

lucasite
Starting Member

4 Posts

Posted - 2005-05-18 : 12:20:10

Hi folks,

I thought it was an easy query. But then, I wasted an entire day trying different techniques. Can anyone help?

I have 3 tables, Orders, OrderDetails and a Products table.
I need a query which returns (for a given date range) how many orders contained a specific Product, and what was the net sum of the orders for that criteria.
The table structure is as follows:

Orders:
- OrderID
- OrderDate
- OrderGross
- OrderNet

OrderDetails:
- DetailID
- OrderID
- ProductID
- Quantity
- Price

Products:
- ProductID
- ProductName
- ProductDescription

I've been working on the following query:

SELECT
dbo.Products.ProductName, dbo.Orders.OrderDate, dbo.OrderDetails.OrderID
FROM dbo.Orders INNER JOIN
dbo.t_OrderDetails ON dbo.Orders.orderID = dbo.OrderDetails.OrderID INNER JOIN
dbo.Products ON dbo.OrderDetails.ProductId = dbo.Products.ProductID
WHERE (dbo.Products.ProductName LIKE N'Box%') AND (dbo.Orders.OrderDate > CONVERT(DATETIME, '2005-05-10 00:00:00', 102))

Because the OrderDetails has multiple records for each order, the results I get are duplicated.
I've tried left joins, right joins. I even tried groups, which seemed to kill the server for half hour every time I ran it.

I'm stumped.

Any response gratefully appreciated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-18 : 15:56:51

this doesn't work for you?

SELECT
dbo.Products.ProductName, dbo.Orders.OrderDate, dbo.OrderDetails.OrderID, count(*) as NumOfOrders,
sum(dbo.t_OrderDetails.price) as price
FROM dbo.Orders INNER JOIN
dbo.t_OrderDetails ON dbo.Orders.orderID = dbo.OrderDetails.OrderID INNER JOIN
dbo.Products ON dbo.OrderDetails.ProductId = dbo.Products.ProductID
WHERE (dbo.Products.ProductName LIKE N'Box%') AND (dbo.Orders.OrderDate > CONVERT(DATETIME, '2005-05-10 00:00:00', 102))
group by dbo.Products.ProductName, dbo.Orders.OrderDate, dbo.OrderDetails.OrderID

sample data and table structure in the form of
"create table ..." and "insert into..." statements would be very helpful.

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-18 : 16:07:24

What do you mean by "the net sum of orders for that criteria?" The total order amount for ALL products in those orders, or just for that one particular product in those orders? Why do you need the "net" sum? What is being subtracted?

i.e., if you only have 1 order, and it contains Product A for $20, Product B for $30, and you are filtering your result by Product A, should $20 be returned or $50 ?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources