Hi All,
I'm pretty new to this and am in need of some help writing a query. I'll try to explain.
I have 2 tables
- tbl_accom
- tbl_bookings
The table tbl_accom has 3 columns accomID which is the PK, accomname (accommdation name such as single bedroom and double bedroom) and availno is an integer for the amount of room available.
The table tbl_bookings includes all the guests booking details but the 4 columns I want to concentrate on are book_ID which is the PK, arrivedate, departdate and accomID which is the FK.
What i am trying to achieve is to write a query that pulls out all of the available accommodation that has a availabilty greater than 0 between 2 given dates.
Below is what I have at the moment:
Select accomname, tbl_accom.availno - count(book_id) as availability
From tbl_accom
Left Outer Join tbl_bookings
on tbl_bookings.accomID = tbl_accom.accomID
where tbl_bookings.arrivedate between '2016-09-12' and '2016-10-12' or
tbl_bookings.arrivedate between '2016-09-12' and '2016-10-12' and tbl_bookings.departdate between '2016-01-12' and '2016-10-12' or
tbl_bookings.departdate between '2016-09-12' and '2016-10-12'
Group By tbl_accom.accomname, tbl_accom.availno
The dates I have used just as an example is 2016-09-12 to 2016-10-12 but all the query is pulling out is the 1 record and from what I have read, this is to do with the Where clause. However I don't know how to change this.
Any help at all would be greatly appreciated.
Thanks,