0

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?

1

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.

  • how can i change it in such a way that i can use a range of dates or check btw 3 dates – Zubair Nazer Oliyat May 7 '18 at 7:05
  • 1
    COALESCE() works in all major DBMS prodcuts, not only Postgres. It's IF() that's proprietary. – ypercubeᵀᴹ May 7 '18 at 7:17
  • am new to sql. can you edit the code and post it as an answer – Zubair Nazer Oliyat May 7 '18 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.