Need help with following SQL... anyone?

[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


accommodation Table
AccomodationID (primary)
HotelID
OfferID
insignificant fields

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
hotels (one to many HotelID) accommodation
offers (one to many OfferID) accomodation
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 the same row as Min(price) in
'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!

 

[[MVP] S.Clark]

Do it with two queries
Query 1: Use base tables & apply all criteria and inner joins.
Query 2: Use base table linked to query 1 and perform the Group By

For example:
Q1: Select * from table1 inner join table2 on table1.field = table2.field
where ...
Q2: Select fieldA, Min(fieldB) from table inner join Q1 on table.field =
q1.field GROUP By FieldA