CODEWITHSUNDEEP
sqlsqlite
Munna
Munna

Reputation: 109

select data from multiple condition -SQLite

I have 2 tables room and booking. Trying to make a prototype of hotel management these two table has the following schema image

So the idea is to return all the rooms that are available. To get so, there can be two category. either the room has been checked out which means check out date will be less than today's date(or the next check in date) Or the room has not been booked for at least once (no booking done for this room yet.)

the query is thus little tricky. I managed to only do the first part.

select room_no 
from room,booking 
where room.room_no= booking.rooms 
and (booking.check_out < strftime('%s', 'now'))`

check_in and check_out are stored in epoch time second

How can i edit the query so that it returns the unbooked rooms also?

Upvotes: 1

Views: 748

Answers (3)

CL.
CL.

Reputation: 180260

What both categories have in common is that there exists no booking for the room at the current time.

In SQL, this can be written like this:

SELECT room_no
FROM room
WHERE NOT EXISTS (SELECT 1
                  FROM booking
                  WHERE booking.rooms = room.room_id
                    AND booking.check_out >= strftime('%s', 'now'))

Upvotes: 0

Munna
Munna

Reputation: 109

okay i've managed to get the desired output from the the combined solution of @barmar and @Rich

SELECT room_no FROM room LEFT OUTER JOIN booking ON room.room_no = booking.rooms WHERE booking.rooms is null or booking.check_out < strftime('%s', 'now')

Upvotes: 1

Rich Rousseau
Rich Rousseau

Reputation: 776

Your schema images isn't showing up for me, but here is what I think you need...

You want one query to return both booked and un-booked rooms. So you'll want to change your join condition between the room and booking tables to a "left" join so that all rooms are returned regardless if a row in booking exists. Once you've done this, any rows returned without a joined row from the booking table will be un-booked rooms.

I don't have a SQLite install to test this against, but here is what I came up with...

SELECT room_no,
       CASE WHEN booking.rooms IS NULL THEN 'Not Booked' ELSE 'Booked' END As BookingStat
FROM room
            LEFT OUTER JOIN booking ON room.room_no = booking.rooms
WHERE (booking.check_out < strftime('%s', 'now')
      OR booking.check_out IS NULL)

Upvotes: 1

Related Questions