guru needed - retrieving multiple row values from a separate table while using Min function

[Papachumba]

Hi guys, i have a little problem and was wandering if anyone can help.
I just need my query modified a little bit to allow some extra values
in there...

Tables:

offers Table

OfferID (primary)
OStartValidity (date)
OValidity (date)
OComments
LocationID
some insignificant Values
hotels Table

HotelID (primary)
LocationID
HName
Some other insignificant values
locations Table

LocationID (primary)
LDescription
LArea
prices Table

PriceID (primary)
OfferID
Price
DepartFrom (date)
DepartTo (date)

Relationships:
locations (one to many LocationID) hotels
locations (one to many LocationID) offers
offers (one to many OfferID) prices

My goal is to retrieve a set of rows from 'offers' table where certain
criteria meets, and then for each row retrieved pull in the Minimum
price from the 'prices' table (matching the same OfferID in 'offers'
obviously). This is my problem - i want to be able to pull in
DepartFrom and DepartTo fields from 'prices' table as well and i dont
know how.

This is the current query i have been using to retrieve just the
Min(price) from prices (i have simplified it a bit to enable you to
comprehend it easier, you will find some extra tables in there not
mentioned above, just ignore them - the ones above are the most
important ones). This has worked fine for me, but as i said i cannot
just add prices.DepartFrom and prices.DepartTo to it as it doesnt seem
to work.

SELECT DISTINCT offers.OfferID, offers.OStartValidity,
offers.OValidity, Min(prices.Price) AS strPrice

FROM locations INNER JOIN ((offers INNER JOIN (hotels INNER JOIN
accomodation ON hotels.HotelID = accomodation.HotelID) ON
offers.OfferID = accomodation.OfferID) INNER JOIN prices ON
offers.OfferID = prices.OfferID) ON (locations.LocationID =
hotels.LocationID)

WHERE offers.OStartValidity<=#2006/3/21# AND
offers.OValidity>=#2006/3/21# AND prices.DepartTo>=#2006/3/21# AND
(locations.LocationID='Dubai Beaches')

GROUP BY offers.OfferID, offers.OStartValidity, offers.OValidity
ORDER BY MIN(prices.Price);

I think i might be needing to use a subquery but unfortunately that is
too complicated for my little brain, i need help!

[HK]

I'm not real clear what you trying to do here.

Is this what you are trying to achieve?: "Which Offers find valid
matches in the Prices table, taking date and price into consideration?"

You get an offer to a specific location like this:

OfferID 357
OStartValidity 03/21/2006
OValidity 03/21/2006
LocationID 36 (Dubai Beaches)

Now you want to know what the min price is in the price table for Dubai
Beaches, starting 03/21/2006 but ending date unspecified, price being
per day? And your prices have to be entered and updated manually for
specific begin dates? Do they take end dates or length of stay into
consideration?

What is the difference between OstartValidity and OValidity?

How do you expect accurate results if you ignore the DepartFrom date?

Your query will likely be very complicated, including subqueries and/or
DLookup, DMax, DMin, DCount, etc. functions. I don't have any ideas
how yet.

[Papachumba]

if you have a look at the query in my post it might be a bit easier to
comprehend, its actually quite simple, the system works, it has for the
last 2 years... I just need to pull in some extra fields into the
existing query from a separate table.

Holiday offer is valid from OStartValidity to Ovalidity, so OValidity
is end date.
Each offer has a pricerange, so DepartFrom - DepartTo - Price defined
in 'prices' table.
like so:
offer 234 01/01/06 - 02/02/06 $500
offer 234 03/02/06 - 01/05/06 $600
offer 234 02/05/06 - 01/12/06 $550
offer 235 and so on...

This is what im trying to achieve:

Get offers which havent expired for a specific location from 'offers'
table (this is where OStartValidity is used, only fo determining
whether or not the offer has expired). Easy enough right?

So, now we have a list of valid offers from offers table, we will need
to find the lowest price for each of those offers from 'prices' table.
Logically, you could do an extra SQL query and retrieve a set of dates
and prices for a particular offer from 'prices' based on OfferID
(simple SQL along the lines of SELECT
Min(prices.Price),DepartFrom,DepartTo FROM prices where OfferID=235 and
DepartFrom>today) and then work out what the cheapest price is from
several rows returned - thats what im trying to do .
What im trying to do is integrate it into the first SQL query. I have
already done that above for just the Price itself by using
Min(prices.price) but i also need to pull in the dates for that price.

Does that make it any clearer?

[Papachumba]

sorry in the post above the SQL is incorrect, i was trying to
illustrate
SELECT * FROM prices WHERE OfferID=235 and DepartFrom>today
which returns a number or rows
then find the minimum price out of these rows returned
i just need that row where the minimum price is, the dates and the
price! thats it!

[cjb_kjb]

Papachumba wrote:

sorry in the post above the SQL is incorrect, i was trying to
illustrate
SELECT * FROM prices WHERE OfferID=235 and DepartFrom>today
which returns a number or rows
then find the minimum price out of these rows returned
i just need that row where the minimum price is, the dates and the
price! thats it!

See if this helps:

Create query Q1 = select offerId, min(price) as MinPrice from prices
group by OfferId

Create query Q2 = Select offerId, ........., MinPrice, DepartFrom,
DepartTo
from Offers, Prices, Q1 where Offers.OfferId = Prices.OfferId and
Prices.OfferId = Q1.OfferId and Prices.Price = Q1.Price