I need help with an assignment for school. I’ve only been doing SQL for 2 months and can’t figure this out. My teacher gave me some hints about using self joins. The database has 3 more tables, but I don’t think they are needed here.
The assignment is to write a query that will help hotel staff find double bookings (same room, same date). I’ve made a test database that has a double booking to control the query.
drop database if exists hoteltest;
create database hoteltest;
use hoteltest;
create table Roomreservation(
ResNr int not null,
RoomNr int not null,
FromDate date not null,
ToDate date not null,
primary key (ResNr, RoomNr, FromDate)
);
insert into Roomreservation
values
(51, 102, '2008-12-05', '2008-12-07'),
(51, 103, '2008-12-05', '2008-12-07'),
(51, 104, '2008-12-05', '2008-12-09'),
(52, 201, '2008-12-05', '2008-12-14'),
(53, 102, '2008-12-04', '2008-12-10');
select * from Roomreservation;
Does anyone have a good and easy solution for this?
Honestly, I’m kinda stuck, I’ve been trying different solutions with concat_ws and the dates but with no results.
3
Answers
You need to check for the range of Fromdate and todate. For this, you can use between. You also exclude the same row from the join. And you only extract one from the join since AxB will lead to BxA on the join. Therefore :
You should use an auto-incremented primary key, such as something as the resnumber but unique.
The idea is to join the table on itself, looking for 2 records where the
ResNo
is different, theRoomNr
is the same, and either theFromDate
or theToDate
is between the other record’sFromDate
–ToDate
.I could write the query for you, but i believe in self-learning, especially for school assignments (rather than actual work).
NOTE: don’t get lazy when it comes to naming, e.g.
ResNr
->ReservationNumber
will save you more time in the future when reading queries
There is a, or at least a, double booking on the following dates:
WITH recursive....
is creating a table (dates
) with all dates from the lower value of FromDate to the highest value of ToDate.see: DBFIDDLE