Unanswered: Subquery Counts without using a subquery?
I have two tables, one of people, and one of rooms for people to go in
personID - key
roomID - key
I need one query to give me, for each person.., the personID, personName, the roomName theyre in, and the count of other people also in their room. A room for the roomID of a person may or may not exist.
With the new version of mysql, I can do this query which is what I want:
SELECT personID, personName, roomName, roomCount
FROM People LEFT JOIN (
SELECT Rooms.roomName, COUNT(People.roomID) as roomCount
FROM People LEFT JOIN Rooms USING(roomID)
GROUP BY People.roomID
) AS RoomAndCount USING(roomID)
WHERE People.roomID = X;
Any suggestions on how to do this in one query without using subqueries since my server doesnt have the latest version? Id be willing to add a field to the Rooms table to store the count, but then I dont even know how to query to update that field...