select data from multiple condition -SQLite

Question

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

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?

There's no strftime in MySQL, so I guess you're using SQLite. — Barmar
– Barmar, Commented Jul 22, 2015 at 20:06
It's SQLite . I intentionally added MySQL tag so that other database experts can take a look over this — Munna
– Munna, Commented Jul 22, 2015 at 20:12

Rich Rousseau · Accepted Answer · 2015-07-22 20:18:48Z

1

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)

edited Jul 22, 2015 at 20:18

answered Jul 22, 2015 at 20:03

Rich Rousseau

7761 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Barmar Over a year ago

Your WHERE clause will filter out all the null rows from booking. Change it to WHERE booking.rooms IS NULL OR booking.check_out < ...

Munna Over a year ago

if no rooms are booked yet, then it should return all the rooms i assume. but the query returns empty

Rich Rousseau Over a year ago

Thanks Barmar. I've updated my query to include a null condition on the booking.check_out.

Munna · Accepted Answer · 2015-07-22 20:25:11Z

1

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')

answered Jul 22, 2015 at 20:25

Munna

1092 silver badges12 bronze badges

Comments

CL. · Accepted Answer · 2015-07-23 10:16:48Z

0

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'))

answered Jul 23, 2015 at 10:16

CL.

182k18 gold badges241 silver badges282 bronze badges

Collectives™ on Stack Overflow

select data from multiple condition -SQLite

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related