SQLTeam.com | Weblogs | Forums

Need help with SQL query


#1

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

  1. tbl_accom
  2. 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,


#2

You need parens for your where clause:

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')