Subquery to check date availability

Could you help me with finding a solution to my query problem? I am stuck with it already for a week so I decided to try to ask for help and learn something. I am using MySQL database.

Right now I have a table without the “search feature”.

SELECT
    r.rId,
    r.rName,
    r.rPrice,
    b.capacity
FROM
   room r,
   (SELECT roomId, SUM(bCapacity) AS capacity FROM bed GROUP BY roomId) AS b
WHERE r.rId = b.roomId;

This is the result:

I want to show all rooms in the table that are not taken withing CHECK-IN and CHECK-OUT dates.

I have:

2 inputs:

Check-In date
Check-Out date

3 tables:

room (I take id, name, price),
bed (I sum capacity of beds in the given room),
booking (here I want to check if certain roomId is taken)

And now I have a query that checks if any room is booked within a certain range. And if the dates collide it returns me the table from booking with roomId.

SELECT
    roomId,
    bCheckIn,
    bCheckOut
FROM booking
WHERE (bCheckOut > ? AND bCheckIn <= ?) AND bStatus = 'Upcoming' OR 'Archived';

First ? is Check-In date and second ? is Check-Out date inputs. Here I pass the above dates and returns rooms which are taken in the range of that date.

This is the result:

SUMMARY

Is it possible to combine those two queries so I will get only one table with first query data, but excluding those taken rooms from the second query? I failed with my query merging.

Answer

I think you want not exists:

select r.*
from rooms r
where not exists (select 1
                  from booking b
                  where b.roomid = r.id and
                        b.checkindate < :end_date and
                        b.checkoutdate >= :start_date
                 );

If you are learning SQL, you should learn proper, explicit, standard, readable JOIN syntax. Never use commas in the FROM clause.

Advertisement

Answer