Just wondering if you guys can help me out a bit, I wrote an availability search a while back.
The availability table has:
ID (Primary Key)
Villa (Foriegn Key)
Price
Availability
Therefore each villa has 52 entries, i.e 1 per week
The search appeared to run fine, until I tested it over a 14 day period, then I realised there was a problem.
At the moment to indicate availability I have used 0 > Not available, 1 > Available
However I think this is causing me problems, at the moment the SQL I am using looks like this:
SELECT * FROM villas LEFT OUTER JOIN pricing ON villas.id = pricing.id WHERE pricing.week >= 1090450800 AND pricing.week <= 1091660400 AND availability = 1
Therefore as long as 1 of the 2 weeks is available it will return a result. Instead if it is searching a 14 day period I need it to a calculation and if the availability equals 2 then it will return the villa as available.
Sorry if this is a little long winded but is there is a facility in SQL to do a sum on two fields???
