Using a Query (http://forums.devshed.com/mysql-help-4/using-a-query-to-determine-what-is-left-over-worked-600982.html)duc nhom (http://www.davistar.com.vn/home/html/)
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.
This is not a MySQL support forum.