CODEWITHSUNDEEP
mysqlsqldatabasedate
Argoitz
Argoitz

Reputation: 349

Mysql - Select rooms where have all the dates

Hi there I have the availability of rooms in a table like this:

room_id |          date       
   1    | 2018-06-09 00:00:00 
   1    | 2018-06-10 00:00:00 
   1    | 2018-06-11 00:00:00 
   2    | 2018-06-09 00:00:00 
   2    | 2018-06-11 00:00:00 
   3    | 2018-06-09 00:00:00 
   4    | 2018-06-07 00:00:00 
...

I send an array of dates and I want to get if any room have availability for all days:

SELECT rtd.room_id, CASE WHEN Count(rtd.room_id) < 2 THEN 'no' ELSE 'yes' END
FROM room_type_day as rtd
WHERE 
rtd.date IN ('2018-06-09 00:00:00', '2018-06-10 00:00:00', '2018-06-11 00:00:00')
GROUP BY rtd.room_type_id

But this return me:

1  | yes
2  | yes
3  | yes
4  | no

How can I find the rooms that are available all the days?

Thank you.

EDIT

If I add HAVING(COUNT DISTINCT rtd.date) = 3" before "group by", I get this error: enter image description here

EDIT

This works for me:

SELECT `room_id`
FROM `room_type_day`
WHERE `date` IN ('2018-06-09', '2018-06-10', '2018-06-11')
GROUP BY `room_id`
HAVING COUNT(`room_id`) = 3;

But how can I modify this query if one or more rooms is available, return "yes" and if no one, return "no"?

Thak you

Upvotes: 0

Views: 64

Answers (3)

cdaiga
cdaiga

Reputation: 4939

You are grouping by a column which is not found in the table. 

SELECT 
   A.room_id, 
   IF(A.count=A.count_in_range,'Yes','No') status
FROM 
(SELECT 
   room_id, 
   COUNT(*) count, 
   SUM(IF(date IN ('2018-06-09 00:00:00', '2018-06-10 00:00:00', 
                   '2018-06-11 00:00:00'),1,0)) count_in_range
FROM room_type_day
GROUP BY room_id) A;

See Demo on SQL Fiddle.

Upvotes: 0

Ullas
Ullas

Reputation: 11556

Use GROUP BY and HAVING clause.

Query

SELECT `room_id`
FROM `room_type_day`
WHERE `date` IN ('2018-06-09', '2018-06-10', '2018-06-11')
GROUP BY `room_id`
HAVING COUNT(`room_id`) = 3;

Find SQL Fiddle here

Upvotes: 2

Dataichou
Dataichou

Reputation: 317

try it like this and make sure that the days_= amount of days in your 'IN' statement

    SELECT room_id, CASE WHEN days_=3 THEN 'yes' ELSE 'No' END) as availability
    FROM
    (
      SELECT rtd.room_id, Count(distinct rtd.room_id)  as days_
      FROM room_type_day as rtd
      WHERE rtd.date IN ('2018-06-09 00:00:00', '2018-06-10 00:00:00', '2018-06-11 00:00:00')
      GROUP BY rtd.room_type_id
     ) as sub

Upvotes: 0

Related Questions