Mysql – Sql availability query error

MySQL

I have a query to fetch the available rooms from 2 tables, roomtypes and reservations, as follows:

Roomtypes has the following columns

id,
product_id,
type,
total_no_of_rooms,
created_at,
updated_at

Reservation has the following columns

id, 
roomtype_id, 
customer_id, 
check_in, 
check_out, 
no_of_rooms, 
created_at, 
updated_at,

This is my sql query:

SELECT rt.id, rt.type, rt.total_no_of_rooms, 
    SUM(r.no_of_rooms) as "reserved rooms", 
    rt.total_no_of_rooms - SUM(r.no_of_rooms) as "rooms available"  
FROM Roomtypes rt 
    LEFT OUTER JOIN Reservations r ON r.roomtype_id = rt.id 
        AND '2018-04-11 10:00:00' >= r.check_in 
        AND '2018-04-12 09:59:59' <= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

When there is a reserved room in the given date, the column shows values to rooms available and rooms reserved. But if no rooms are reserved, the column shows null as values where as I need to get the rooms available as number of rooms available and reserved rooms as 0. How can I get this?

Best Answer

Try using the following query (Considering you are using mysql):

SELECT rt.id, rt.type, 
    rt.total_no_of_rooms, 
    if(SUM(r.no_of_rooms) IS NULL,0,SUM(r.no_of_rooms)) as "reserved rooms", 
    (rt.total_no_of_rooms - if(SUM(r.no_of_rooms) IS NULL,0,SUM(r.no_of_rooms))) as "rooms available"
FROM Roomtypes rt LEFT OUTER JOIN Reservations r 
    ON r.roomtype_id = rt.id 
    AND '2018-04-11 10:00:00' >= r.check_in 
    AND '2018-04-12 09:59:59' <= r.check_out 
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

I have just added condition to check NULL value. If you are using PostgreSQL, use COALESCE function.

Related Solutions

MySQL Database Design – Clarification for Database Creation and Relations

Just for a start:

Usually, this should become clear from normalizing the data. You need a separate table for each entity. And you require an intermediate table for many to many (m:n) relations.

With hotel and room/reservation, it's unlikely that a specific room/reservation relates to more than a single hotel. For meals, you need to decide, what exactly defines one. If all you care is, e.g., whether or not cheese sandwiches are served in some particular hotel, it'd look like m:n. You will need to ask you(r domain expert), what will be true for the lifetime of the system, you are working on.

Foreign keys will be needed, no matter, whether you have intermediate tables or not - the difference is, where they are stored.

Looking at your current schema: Two things come to mind. Either you are storing optional attributes in the intermediate tables - and you take the shortcut, if no optional attribute is filled in. Or you do actually have multiple relations between these entities. You would need to check your data to differentiate between the two. I'd usually vote for consistency - thus using one approach for storing a particular type of fact.

Update regarding "hotel_meal": With all this detail in the meal record, I'd doubt one dish to be offered in more than one hotel (unless you have hotel chains in your system, or several hotels' kitchen is run by the same caterer).
You could check:

SELECT
  meal_id
  , COUNT(hotels_id)
FROM meal_hotel
GROUP BY meal_id
HAVING COUNT(COUNT(hotels_id)) > 1;

If it returns any record, you have a reason for an intermediate table - if you decide the records returned do actually make sense in your domain (hotel chains, same caterer - you should know it).

Update II regarding "hotel_meal": If no meal is ever served in more than a single hotel, you should not need the additional table; just have the hotel_id as foreign key in the meal table. Just as with rooms and reservations…
To list all meals for a particular hotel:

SELECT * FROM meals WHERE hotel_id=1;

Feel free to come back with more detail.

Mysql – Update Column in table on midnight daily

You can write a simple script externally or use the MySQL event scheduler. Both will work just fine. The benefit of the MySQL event scheduler is that it will be part of your database backups.

Related Question