I have a database table that stores the cost of a single night's stay
at various hotels, offered by various vendors:

REATE TABLE [dbo].[Rates] (
	[ResortID] [int] NOT NULL ,
	[VendorID] [int] NOT NULL ,
	[DateofStay] [datetime] NOT NULL ,
	[Cost] [float] NOT NULL ,
	[DateSearched] [datetime] NOT NULL ,
	[RoomType] [int] NOT NULL 
)

Each vendor can supply rooms for multiple hotels.  For example if
Expedia is a vendor, they can offer hotel rooms for Holiday Inn, Best
Western, and so on.  Other vendors may also offer hotel rooms for
these hotels.  Vendors do not have to offer every hotel, however.

Vendor's don't have to offer a room every night, either.  So Expedia
can offer a room at the Holiday Inn on August 1, 2, 3, 4, and 6, but
not August 5 (because it may be sold out).

My question is how I would write an SQL query (using MySQL 3.23.58)
that finds the least expensive vendor for every hotel room in the
table?  So if ten different vendors offer rooms at the Holiday Inn,
and they've all got availability for the dates I want, how do I find
which vendor is the least expensive?

Hi

Try this type of sample sql query. i am not sure about Mysql. just an idea. 

SELECT vendorid from table1 where resortid=xxx and cost=(select
min(cost)  from table1 where resortid=xxx)

xxx - resortid

thanks
bala

Add "and DateOfStay = yyy" into the where clause of the subquery, with
yyy being the requested date of stay.

More realistically you'd probably want to be able to specify a stay of
several nights and purchase the whole block in a single transaction.
It should go something like this: (NB I'm only familiar with Oracle
SQL, so some translation may be required)

select vendorid, cost from
    (select vendorid, sum(cost) as cost from
        (select vendorid, dateofstay, cost from Rates
        where resortid = xxxx 
        and dateofstay >= yyyy and dateofstay <= zzzz
        and roomtype = wwww) Sub1
    group by vendorid 
    having count(*) = (zzzz - yyyy + 1)) Sub2
where cost = (select min(cost) from Sub2)

xxxx = desired resort ID
yyyy = start of stay
zzzz = end of stay
wwww = desired room type
Note: (zzzz - yyyy) + 1 = number of days staying

Will not consider vendors who do not have the desired resort available
for the full length of stay. Assumes there is only (at most) one
record in the DB with a given resort ID, vendor ID, date of stay and
room type. This may not be so, given that you have a DateSearched
field in the table.