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;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.