Page 1 of 1

Select where count() > 1

Posted: Thu Sep 14, 2006 8:37 am
by onion2k
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:

Code: Select all

Table: pre_room
room_id
name

table: pre_allocation
allocation_id
room_id
date
allocation
If my query I select all of the allocations in a set date range where the allocation is greater than a particular number .. eg

Code: 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_id
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.

Posted: Thu Sep 14, 2006 8:50 am
by ody
Just stick `having total >=2` at the very end of your query.

Posted: Thu Sep 14, 2006 9:17 am
by onion2k
doh .. of course .. I always forget about that because I so rarely use it. Works like a charm. Cheers.