I need some kind of Join, that's my best guess...

[Justin Sampson]

Hello everyone,
I have a table that looks like this:

Seasons:

seasonID
seasonPID
seasonFromdate
seasonTodate
seasonNightlyPrice
seasonWeeklyPrice
seasonMonthlyPrice
seasonWeekendPrice

...and another table like this:

Cottages:

cotID
cotName
cotAddress

The "seasonPID" field corresponds to the "cotID" of the records in Cottages table.

Now what I want to do is run a query that will list the records in the cottage table by price, and that data is found in the Seasons table. The catch is, that:

A). Each cottage can have from 0 to 5 seasons (each season is an entry in the Seasons table), and I want to make sure I order by the price of the current season (that what the From and To fields are for, they are the start and end dates). Also, if there are no records in the Seasons table, the cottage should be displayed at the end (instead of at the start with a price of $0)

B). A season might have just one, or all of the Prices inputed (prices are inputed for Nightly, Weekly, Monthly, and Weekend). I want the query to take whichever is lowest (other then $0) and use that to order the results. So if one cottages current season has a nightly price of $80, and another cottage has a weekend price of $70, the one that is $70 would be displayed first. What I'm trying to say is that not all records need to ordered by the same price field, simply whatever price is lowest.

Any input would be great, because I have no idea where to start.

Best regards,
Justin