Hello. I am writing a script for booking hotel rooms. There are two tables:
allnomer http://sqlfiddle.com/#!9/75102 where all numbers are listed. The hotel has 3 lux rooms: 301, 302 and 303.
And main http://sqlfiddle.com/#!9/02c4e where all the bookings are listed. The purpose of the script is to check the numbers for availability, and if a free number is found, it issues this number to the reservation.
I have a check-in and check-out date for a new reservation: 2016-11-28 and 2016-11-30. This is a date range. That is, on the 28th, 29th and 30th, people are going to live in the hotel. Two rooms have already been booked in the hotel. The first (301) for the dates from 2016-11-29 to 2016-12-01, the second (302) for the dates from 2016-12-01 to 2016-12-02. A person cannot call 301 numbers, since on the 29th and 30th this number will be busy. We have two numbers left: 302 and 303. The 302 number is busy only 01 and 02. No date intersects and therefore the 302 number suits us. The $ namenomer variable takes on the value 302 and is substituted into the script.
Tell me how to check if a number is free so that, in case of at least one match, the next number is checked? The probability that at this stage there will not be a single free number is excluded. Before this stage, there is already a script that checks all numbers for availability. However, he checks all the numbers. The same script is needed to check certain numbers.
Here is the code I have now:
SELECT a.nomer FROM allnomer a LEFT JOIN main m ON a.nomer = m.numbernomer AND ( m.datestart BETWEEN '2016-11-28' AND '2016-11-30' OR m.dateend BETWEEN '2016-11-28' AND '2016-11-30' ) WHERE a.TYPE = 'lux' AND m.id IS NULL
And he processes these dates correctly. I get a 302 response.
But if the structure of the main table is like this sqlfiddle.com/#!9/fba1bd, and the query is like this:
SELECT a.nomer FROM allnomer a LEFT JOIN main m ON a.nomer = m.numbernomer AND ( m.datestart BETWEEN '2016-12-23' AND '2016-12-23' OR m.dateend BETWEEN '2016-12-23' AND '2016-12-23' ) WHERE a.TYPE = 'lux' AND m.id IS NULL
Then 301 is returned, even though 23 is in the range of numbers in the main table. The same is with all numbers that are in this interval, except for the 19th and 25th numbers.
SET @start = '2016-11-28'; SET @end = '2016-11-30'; SELECT a.nomer FROM allnomer a LEFT JOIN main m ON a.nomer = m.numbernomer AND DATEDIFF(m.datestart, @end) * DATEDIFF(m.dateend, @start) <= 0 -- AND a.type = 'lux' WHERE m.numbernomer IS NULL -- ORDER BY 1 -- LIMIT 1 ;
If you need a strictly non-superimposition (i.e. if the number had been vacated on the 28th or occupied on the 30th, then it would not have worked), then replace the inequality with a strict one.