如何从db获取记录？

Question

I worked in travel CRS protal. for hotel booking i want to block hotel depends on the cutoff dates.

 SELECT * FROM (`crs_service_supplier_details` as crs) JOIN `crs_service_room_type_detail` as Room ON `crs`.`service_apartment_id`=`Room`.`service_id` JOIN `crs_room_type` as crt ON `Room`.`room_type_id`=`crt`.`room_type_id` JOIN `crs_service_off_dates` as ccod ON `crs`.`service_apartment_id`=`ccod`.`co_service` WHERE `crs`.`city` like '%Chennai%' AND ('2016-10-27' NOT between ccod.cut_off_from AND ccod.cut_off_from) AND ('2016-10-28' NOT between ccod.cut_off_to AND ccod.cut_off_to) AND `Room`.`edate` > '2016-10-27' GROUP BY `crs`.`service_apartment_id`

this is the query but the problem was in cut had two days

co_id   co_service  cut_off_from    cut_off_to  cut_off_release_days cut_off
1       1       2016-10-20      2016-10-22          0               2016-10-20
2       1       2016-10-27      2016-10-28          0               2016-10-28

if i searched between 2016-10-20 and 2016-10-22 it get the "2016-10-27 and 2016-10-28" and show the result.

if i searched between 2016-10-27 and 2016-10-28 it get the "2016-10-20 and 2016-10-22" and show the result. it wont block any where of the system. please give some idea to solve this?

Answer 1

May be your query is not right. May be this will help, just change in BETWEEN logic:

AND ('2016-10-27' NOT BETWEEN ccod.cut_off_from AND ccod.cut_off_to)
AND ('2016-10-28' NOT BETWEEN ccod.cut_off_to AND ccod.cut_off_from)

use the above.

The original code with BETWEEN doesn`t make sense:

AND ('2016-10-27' NOT BETWEEN ccod.cut_off_from AND ccod.cut_off_from)
AND ('2016-10-28' NOT BETWEEN ccod.cut_off_to AND ccod.cut_off_to)

Because it is making between on the same column.