Select where count() > 1
Posted: Thu Sep 14, 2006 8:37 am
I'm joining two tables together. The first table is a list of rooms, the second is list of allocation available with one row per date. The relevent bits of tables are:
If my query I select all of the allocations in a set date range where the allocation is greater than a particular number .. eg
The point of this is that count(allocation_id) as total should equal the number of days between the date and the date + 2 days if there are rooms available on every date .. if there aren't then it'll be less.
Is there any way I can add something so that rooms where total is less than 2 are NOT returned? I can't put the count, or it's alias (total) into the where clause without MySQL complaining.
Code: Select all
Table: pre_room
room_id
name
table: pre_allocation
allocation_id
room_id
date
allocationCode: Select all
select r.room_id, count(allocation_id) as total from pre_room r, pre_allocation a where r.room_id = a.room_id and ('2006-09-15' <= a.date and adddate('2006-09-15', interval 2 day) >= a.date) and a.allocation > 5 group by r.room_idIs there any way I can add something so that rooms where total is less than 2 are NOT returned? I can't put the count, or it's alias (total) into the where clause without MySQL complaining.