Subquery to check date availability

Question

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.

score 1 · Accepted Answer · answered Jun 08 '20 at 12:16

1

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.

answered Jun 08 '20 at 12:16

Gordon Linoff

1,242,037
58
646
786

"Never use commas in the FROM clause" - I suppose that's a bit of a polarizing issue :) (https://stackoverflow.com/questions/7810515/is-there-an-oracle-official-recommendation-on-the-use-of-explicit-ansi-joins-vs). – dcp Jun 08 '20 at 12:29
@dcp . . . Everybody is, of course, entitled to their opinions. But a 10-year old screen concerning shortcomings in the Oracle optimizer isn't really application to a MySQL question. – Gordon Linoff Jun 08 '20 at 14:06
Fair enough. However, your comment started with "If you are learning SQL...", which is more of a general statement, also not really specific to MySQL. I agree that there are good reasons to use ANSI JOIN syntax, but "Never use commas in the FROM clause" is more of a very strong opinion towards a specific coding style, and I think your answer would have been better if you had indicated that point. As you said, everybody is entitled to their opinions, and coding style preference is indeed, an opinion. – dcp Jun 08 '20 at 14:26
Thanks a lot, I combined those two queries with your example and got wanted result :) – Alp3n Jun 09 '20 at 05:21

score 0 · Answer 2 · answered Jun 09 '20 at 05:27

Here is the query I combined with Gordon Linoff suggestion

SELECT r.rid            AS 'ID', 
   r.rname          AS 'Room Type', 
   r.rprice         AS 'Price (THB)', 
   Sum(b.bcapacity) AS 'Max Guests' 
FROM   room r 
       LEFT JOIN bed b 
              ON r.rid = b.roomid 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   booking bo 
                   WHERE  bo.roomid = r.rid 
                          AND bo.bcheckin < ? 
                          AND bo.bcheckout >= ?) 
GROUP  BY roomid;

And the result:

In this example, I have 2 booked rooms in the BOOKING table for dates of check-in '2020-06-08', check-out '2020-06-10'. I checked the same dates for above queries and it returned only rooms that are not booked within a given range.

Subquery to check date availability

2 Answers2