Complicated SELECT Problem

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

--
JasonMaybe 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|||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|||AS400 Guru (hazen@.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, esquel@.sommarskog.se

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