SQL Server ORDER BY/WHERE with nested select
- by Echilon
I'm trying to get SQL Server to order by a column from a nested select. I know this isn't the best way of doing this but it needs to be done.
I have two tables, Bookings and BookingItems. BookingItems contains StartDate and EndDate fields, and there can be multiple BookingItems on a Booking. I need to find the earliest startdate and latest end date from BookingItems, then filter and sort by these values.
I've tried with a nested select, but when I try to use one of the selected columns in a WHERE or ORDER BY, I get an "Invalid Column Name".
SELECT b.*, (SELECT COUNT(*) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS TotalRooms,
(SELECT MIN(i.StartDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS StartDate,
(SELECT MAX(i.EndDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS EndDate
FROM bookings b LEFT JOIN customers c ON b.CustomerID = c.CustomerID WHERE StartDate >= '2010-01-01'
Am I missing something about SQL ordering? I'm using SQL Server 2008.
