Using a Query to determine what is left over... wo

duc nhom

I may have to find a different way to make this work...

I have three tables: availcabins, weeks & cabinorders

availcabins is a listing of cabins that are available
weeks is a listing of weeks that the camp operates
and cabinorders contains details about which cabins that have been ordered and for what week.

As long as a cabin is made visible and available (1, 0 checkboxes) then it would be available for every week, unless it is already rented, then it wouldn't be available for that week..

Previously in MySQL 4.x this query would give me a listing of cabins that had not been put into an order yet:

Code:

SELECT availcabins.rCabin_name
 , availcabins.rCabin_ID
 , availcabins.rCabin_Wcost
 , availcabins.rCabin_displayOrder
  , cabinorders.cabinOrder_orderID
, weeks.campweek_name
, weeks.campweek_start
, weeks.campweek_length
 FROM ((availcabins LEFT JOIN cabinorders ON cabinorders.cabinOrder_cabinID=availcabins.rCabin_ID)
 RIGHT JOIN weeks ON weeks .campweek_ID=cabinorders.cabinOrder_weekID)
 WHERE availcabins.rCabin_campAvail=1
  AND availcabins.rCabin_visible=1
  AND cabinorders.cabinOrder_orderID is null
  AND weeks.campweek_length<=7
 ORDER BY availcabins.rCabin_displayOrder ASC
, weeks.campweek_start ASC



This query doesn't return the same results in MySQL 5.x.

From what I can tell I need a way to tell the query that every cabin should have every week available, but I don't have a table that does that.

Is there another solution to this without me having join the cottages to the weeks with another table?

Thanks in advance.