Sql query for room availability I have the following tables in my database:
1.Roomtypes
2.Reservations

Roomtypes have 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,

i need to get the rooms remaining in roomtype table if someone books a room. that is details no of rooms in a specific date,rooms which are not in reservations. can anyun help me with the query
My best guess, in the absence of a table CREATE script, would be something like:

SELECT rt.id, rt.type, rt.total_no_of_rooms, 
    IFNULL(SUM(r.no_of_rooms), 0) as "reserved rooms", 
    rt.total_no_of_rooms - IFNULL(SUM(r.no_of_rooms),0) as "rooms available"  
FROM Roomtypes rt 
    LEFT OUTER JOIN Reservation r ON r.roomtype_id = rt.id 
        AND CURRENT_TIMESTAMP BETWEEN r.check_in AND r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms


This query should get the number of rooms available for each room type now (CURRENT_TIMESTAMP), but you can replace that with any other time stamp, e.g. '2018-05-17 10:00:00'.

To see how many rooms are available within a data range - check-in at 2018-04-11 10:00:00 and latest check-out at 2018-04-12 09:59:59:

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


Here's an sqlfiddle example (MySQL 5.6) with create script.