Complicated SELECT Problem

[Jason]

I'm having some trouble coming up with the correct select statement.
Lets say I have the following two tables:

---------------- ----------------
Orders OrderItem
---------------- ----------------
PK OrderID (int) PK OrderItemID (int)
OrderDate (datetime) FK OrderID (int)
CustomerName (nvarchar) Priority (int)

My search would basically return a list of all the orders placed within
the last week. The select statement would include the entire contents
of the Orders table. The tricky part is that I also need to return the
number of items within each order that have a priority of 1 (so they
can be displayed more prominently).

The first part of the select statement is easy enough, but I'm stuck on
the count part. I'd appreciate any help. Here's what I have so far:

SELECT
Orders.OrderID,
Orders.OrderDate,
Orders.CustomerName
FROM
Orders
WHERE
DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7

--
Jason

[AS400 Guru]

Maybe I am missing something. I didn't understand how priority of 1
mattered. The field wasn't in your database structure.

SELECT
Orders.OrderID,
Orders.OrderDate,
Orders.CustomerName,
(select count(orderid) from orderitem where
orders.orderid=orderitem.orderid) as Count
FROM Orders
WHERE DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7
Jason wrote:

I'm having some trouble coming up with the correct select statement.
Lets say I have the following two tables:

---------------- ----------------
Orders OrderItem
---------------- ----------------
PK OrderID (int) PK OrderItemID (int)
OrderDate (datetime) FK OrderID (int)
CustomerName (nvarchar) Priority (int)

My search would basically return a list of all the orders placed within the last week. The select statement would include the entire contents of the Orders table. The tricky part is that I also need to return the number of items within each order that have a priority of 1 (so they
can be displayed more prominently).

The first part of the select statement is easy enough, but I'm stuck on the count part. I'd appreciate any help. Here's what I have so far:

SELECT
Orders.OrderID,
Orders.OrderDate,
Orders.CustomerName
FROM
Orders
WHERE
DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7

--
Jason

[Jason]

Thanks. That should work just fine. I was unaware that you could
include a select statement inside another select like that. You learn
something new every day.

--
Jason

[Erland Sommarskog]

AS400 Guru (ha***@candid.com) writes:

Maybe I am missing something. I didn't understand how priority of 1
mattered. The field wasn't in your database structure.
It is in OrderItem.
SELECT
Orders.OrderID,
Orders.OrderDate,
Orders.CustomerName,
(select count(orderid) from orderitem where
orders.orderid=orderitem.orderid) as Count
FROM Orders
WHERE DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7

This is likely to perform better:

SELECT o.OrderID, o.OrderDate, o.CustomerName, coalesce(oi.cnt, 0)
FROM Orders o
LEFT JOIN (SELECT OrderId, cnt = COUNT(*)
FROM OrderItem
WHERE Priority = 1
GROUP BY OrderId) oi ON o.OrderID = oi.OrderID
WHERE DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7

It's always a good idea to benchmark different solutions. But my experience
is that a derived table gives better performance that sub-selects in the
SELECT list. The latter are usally computed for each row, whereas the
derived table permits the optimizer to do all at once.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp