is it possible to use as a join condition the result of a function like COUNT?
a left join b on b.key = count(a.key)
count(a.key) as C
a left join b on b.key = C
is it even possible to reference a column from the select clause in the join conditional?
select 5 as Five a left join b on b.key = Five;
what im trying to do: i have a table of rates per person in a room... which rate to use for a person depends on the total number of people in the room which i get by using count. im assuming i could accomplish this with subqueries.. but i dont have the latest version of mysql on my host.
what you want is most likely possible, except all your a's and b's and c's are too confusing, and your people and rates and rooms are pretty nebulous as far as knowing which columns are in which tables
yes, just take the first query and "nest" it inside the second as a derived table
, count(people.personID) as RCount
on people.roomID = rooms.roomID
) as roomcount
on people.roomID = roomcount.roomID
on roomcount.RCount = Rates.RCount