Help: Query(Solved)

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

Moderator: General Moderators

User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Code: Select all

SELECT r.Name, r.MaxUsersAllowed, COUNT(DISTINCT u.UserName) as CurUsers FROM ChatRooms_tbl AS r JOIN ChatUsers_tbl AS u ON (r.Id=u.ChatRoomId) GROUP BY r.Name, r.MaxUsersAllowed HAVING CurUsers < r.MaxUsersAllowed;
This should give a list of
Room Names, MaxInRoom, Current in Room
for all rooms with open space.

should be pretty close... (I stuck r.MaxUsersAllowed into the SELECT list because some DBMS's require all HAVING columns to be in the SELECT list....) The DISTINCT is probably not needed.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

good work nielson

I used your idea to change it to the way I wanted

Code: Select all

SELECT (r.MaxUsersAllowed >=  count(u.UserName)) AS UserAllowed
FROM ChatRooms_tbl as r, ChatUsers_tbl as u 
where  r.Id = u.ChatRoomId  and  r.Id = 1
group by r.Name
(that r.id = 1 will be the chatroomid in the session variable.)

This would finally tell whether the user is allowed to enter the chat room or not.

I earlier was saying that I do not need having cos I dont use the aggregate function, count(*) in the where clause.

I have to work on joins as I still find it hard how and when to use a join

anyway, cheers to feyd and nielson
Post Reply