Not sure what you are asking for. If you are trying to get the
opposite of what you are getting, try adding
AND student_accom.start_date IS NULL
to the end of the query. You can actually use just about any field
instead of start_date.
For easier readability, you can rephrase your query to clarify the join.
SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN
student_accom
ON accomodation.accom_id = student_accom.accom_id AND
((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'))
WHERE student_accom.start_date IS NULL
It may actually end up being faster by moving your original WHERE to
the join.
On Nov 16, 2005, at 11:54 PM, [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/mysql?
[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
