How to only return queries where all of the records match the where statement

Question

0.00/5 (No votes)

See more:

I am making an hotel reservation app with a database that contains the entity "Reservation" and "Room". The attributes used from the database are as follows: Reservation contains startdate, enddate and Room.Id. Room contains Id and roomnumber.

I am trying to write an SQL query that only returns rooms if the new startdate and enddate do not fall between the already existing startdate and enddate of all the rooms. So if a room is free, the roomnumber should be returned.

The problem is that if there are 2 reservations of 1 room with different dates, where one record start and enddate do fall between the new start and enddate, and the other record does not, it will still return the roomnumber as free even tho it is not. So i need a query that will return the roomnumber only if all of the start and enddates of a reservation records of a certain room are free.

Underneath is an example of what i have right now, but this does not return any records since just one of the records does match the where statement.

The @NewStartDate is a local variable and the {entitys} and [attributes] must be written with that syntax. Does anyone know the solution to my problem?

What I have tried:

SELECT {Room}.*
FROM {Room}
INNER JOIN {Reservation} ON {Room}.[Id] = {Reservation}.[RoomId]
WHERE @NewStartDate < ALL (
SELECT {Reservation}.[StartDate]
FROM {Reservation}
WHERE {Reservation}.[StartDate] > GETDATE()) and @NewStartDate > ALL (
SELECT {Reservation}.[StartDate]
FROM {Reservation}
WHERE {Reservation}.[EndDate] > GETDATE())
GROUP BY {Room}.[RoomNumber], {Room}.[Id]

Data:

Reservation.Id Reservation.Startdate Reservation.Enddate Room.Id Room.Roomnumber
1                    2016-01-01              2016-01-03           1        1
2                    2016-01-01              2016-01-03           2        2
3                    2016-01-02              2016-01-02           3        3
4                    2016-02-05              2016-02-08           1        1
5                    2016-02-25              2016-03-01           2        2
6                    2016-02-26              2016-03-02           1        1
7                    2016-03-01              2016-03-03           3        3

Posted 20-Feb-22 21:43pm

Member 15542389

Updated 20-Feb-22 22:35pm

v2

Add a Solution

Comments

_Asif_ 21-Feb-22 6:28am

How about this?
DECLARE @TBL TABLE
(
RESERVATION_ID INT,
START_DATE DATETIME,
END_DATE DATETIME,
ROOM_ID INT
)

INSERT INTO @TBL
SELECT 1, '2016-01-01', '2016-01-03', 1
UNION ALL
SELECT 2, '2016-01-01', '2016-01-03', 2
UNION ALL
SELECT 3, '2016-01-02', '2016-01-02', 3
UNION ALL
SELECT 4, '2016-02-05', '2016-02-08', 1
UNION ALL
SELECT 5, '2016-02-25', '2016-03-01', 2
UNION ALL
SELECT 6, '2016-02-26', '2016-03-02', 1
UNION ALL
SELECT 7, '2016-03-01', '2016-03-03', 3
UNION ALL
SELECT 1, '2015-12-29', '2016-01-02', 4
UNION ALL
SELECT 1, '2015-01-01', '2016-01-04', 5

DECLARE @RNewSTartDate DATETIME = '2016-01-01'
DECLARE @RNewEndDate DATETIME = '2016-01-04'

select distinct ROOM_ID
from @TBL
where NOT @RNewSTartDate BETWEEN START_DATE AND END_DATE
AND NOT @RNewEndDate BETWEEN START_DATE AND END_DATE
AND NOT @RNewSTartDate >= START_DATE AND @RNewEndDate <= END_DATE

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2022-02-20T21:54:00

Solution 1

Without your actual data we can't really tell - but the problem is probably your GROUP BY clause: every field you add there increases the number of returned rows, not reduces them. This may help: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

Posted 20-Feb-22 21:54pm

OriginalGriff

Comments

Member 15542389 21-Feb-22 4:30am

I dont think its the group by statement because i dont get an error and i do receive the data i expect, its just not correct.

I have added the data to make it more clear. So lets say i want to make a new reservation with the startdate being on 2016-01-01. The query checks if that date is taken (which it is on reservation id 1 and 2) and it wont return those rooms as available. But on reservation 4 and 5 the new reservation date 2016-01-01 does not fall between those dates so it will still return the rooms even though they are not available. So i want a query that checks if all the records from a certain room are clear so it will return a room only if there is no reservation on the new date 2016-01-01

Andre Oosthuizen · Answer 2 · 2022-02-20T22:35:00

See an almost exact question and answer HERE

Your code should then look similar to this -

$query = sprintf(
        "SELECT `id`, `maxGuests`
        FROM `room`
        WHERE `id` NOT IN
        (
            SELECT `roombooking_room`.`room_id`
            FROM `roombooking_room`
            JOIN `roombooking` ON `roombooking_room`.`roombooking_id` = `roombooking`.`id`
            WHERE `roombooking`.`confirmed` =1
            AND (`roomBooking`.`startDate` > DATE(%s) OR `roomBooking`.`endDate` < DATE(%s))
        )
        AND `maxGuests` <= %d ORDER BY `maxGuests` DESC",
        $endDate->toString('yyyy-MM-dd'), $startDate->toString('yyyy-MM-dd'), $noGuests);
        $result = $db->query($query);
        $result = $result->fetchAll();

        $rooms = array();
        $guests = 0;
        foreach($result as $res) {
            if($guests >= $noGuests) break;
            $guests += (int)$res['maxGuests'];
            $rooms[] = $res['id'];
        }