Your query was: SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN student_accom ON accomodation.accom_id = student_accom.accom_id where ((student_accom.start_date Between '2005-10-01' And '2005-11-30') OR (student_accom.leave_date Between '2005-10-01' And '2005-10-30'))
The logic: find me all accomodations that have a start date in October or November, or have an end date in October or November. That way, you find all the bookings that start Oct. 1-Nov. 30 (was that a typo in that last '2005-10-30'?). You'll also find the bookings that start sometime before Oct. 1, and end sometime in October. You want a query whose logic is "find me all accomodations that meet both of the following qualifications: 1) there are no bookings starting in Oct. and Nov, and 2) there are no bookings ending in Oct. and Nov. As has been said many times on this list, it's very difficult to find data that's NOT in the database. Also, did I miss the fact that there's an operator called "Between"? or is that a pseudo-query? (I'm coming back to MySQL after some time off, so between may be valid). Sounds like you want something like: SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN student_accomd on accomodation.accom_id=student_accom.accom_id where ((student_accom.start_date not between '2005-10-01' and '2005-11-30') AND (student_accom.leave_date not between '2005-10-01' and '2005-11-30')) This also follows the math logic that the inverse of "this or that" is "'not this' and 'not that'". -Sheeri On 11/16/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hello, > I'm running mysql 4.0.12 > > I have a bookings database in which I'm having problems pulling out the > available accomodation. > > Tables are: > Accomodation - accomid (primary key), town, address, contactnumber etc > student_accom - studentaccomid (primary key), accomid, studentid > > My query is as follows so far, this is pulling out all the accomodation that > is > booked between specified dates - not what I'm after - > > SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN > student_accom > ON accomodation.accom_id = student_accom.accom_id where > ((student_accom.start_date Between '2005-10-01' And '2005-11-30') OR > (student_accom.leave_date Between '2005-10-01' And '2005-10-30')) > > Appreciate any assistance. > > Thanks > Gavin > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
