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.

CodePudding user response：

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 :

SELECT t1.* FROM Roomreservation t1
JOIN Roomreservation t2 ON t1.ResNr<>t2.ResNr AND t1.RoomNr=t2.RoomNr AND (t1.FromDate BETWEEN t2.FromDate AND t2.ToDate OR t2.FromDate BETWEEN t1.FromDate AND t1.ToDate);

You should use an auto-incremented primary key, such as something as the resnumber but unique.

CodePudding user response：

The idea is to join the table on itself, looking for 2 records where the ResNo is different, the RoomNr is the same, and either the FromDate or the ToDate is between the other record's FromDate-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

CodePudding user response：

There is a, or at least a, double booking on the following dates:

WITH recursive dates as (
    select min(FromDate)  as d FROM Roomreservation
    union all
    select adddate(d, INTERVAL 1 day) 
    from dates 
    where d<(select max(ToDate) from Roomreservation)
)
SELECT 
   RoomNr,
   d,
   count(*)
FROM Roomreservation
CROSS JOIN dates
GROUP BY 
  RoomNr,
  d
HAVING count(*) >1
ORDER BY d, RoomNr

• The WITH recursive.... is creating a table (dates) with all dates from the lower value of FromDate to the highest value of ToDate.
• And then it's just a matter of counting how many reservation there are on that day, for that RoomNr.

see: DBFIDDLE