SQL select available rooms-CodePudding

I work on an hotel app, and I am trying to query my sql db for the available rooms between 2 dates.

The table rate looks like this:

room_id	date	unavailable	price
1	16/08/2022	false	10
1	17/08/2022	null	20
1	18/08/2022	null	30
2	16/08/2022	false	10
2	17/08/2022	true	20
2	18/08/2022	null	30
3	16/08/2022	false	10
3	17/08/2022	false	20
3	19/08/2022	false	30

I am looking for the rooms available between from = 16/08/2022 and to = 18/08/2022, i.e. the room_ids

with a row for every date between from and to
where none of these row has unavailable = true

expected output

room_id
1

CodePudding user response：

use a not exists

  with t as (select * from rate 
     where date BETWEEN '2022-08-16' AND '2022-08-18')
    select distinct room_id from t a where not exists 
    ( select 1 from t b where a.room_id = b.room_id
      and b.unavailable = true)

CodePudding user response：

You can use a GROUP BYwhere you count the available dates and compare it to the dates you nned,

more complex pattern would need more sophisticated algorithms

SELECT "room_id" FROM rooms WHERE  "date" BETWEEN '2022-08-16'::date AND '2022-08-18'::date
AND ("unavailable" = false OR "unavailable" IS NULL)
GROUP BY "room_id"
HAVING COUNT(*) =  EXTRACT(DAY FROM '2022-08-18'::timestamp - '2022-08-16'::timestamp)   1

| room_id |
| ------: |
|       1 |

db<>fiddle here