Select where count() > 1

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Select where count() > 1

Post 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.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Just stick `having total >=2` at the very end of your query.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

doh .. of course .. I always forget about that because I so rarely use it. Works like a charm. Cheers.
Post Reply