How can I use the Count expression to achieve the following:

availability table
room_id property_id date_id available
1 8 1 Yes
1 8 2 Yes
1 8 3 Yes
2 8 1 Yes
2 8 2 No
2 8 3 Yes

Select the room ids that are available for all three date ids without using repetitive AND statements and instead using the following SQL with a count = 3 expression.

SELECT room_id
FROM availability
WHERE availability.property_id=8
AND availability.available=Yes
AND date_id BETWEEN 1 AND 3

This returns:
room_id
1
1
1
2
2

but I only want to return the room_ids that are available for all 3 date_ids i.e. room_id 1.

so I could use the DISTINCT key word but this would return:
room_id
1
2

so I need to also say in an expression "where count each room_id = 3".

Query like this one does what you wanted, but I'm sure there must be a better way to do it ...