I have two tables, one of people, and one of rooms for people to go in

People
------
personID - key
personName
roomID

Rooms
-----
roomID - key
roomName

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

thanks

wow, thank you so much for your quick reply. its working now.

so the group by field was the key... it makes a lot of sense.

i still have to stick to using left joins though because of the case where a room may not exist, but youve pointed me in the right direction.

thanks so much!

select
a.personID,
a.personName,
b.roomName,
count(a.personID) as roomCount
from
People a
left join Rooms b using(roomid)
left join People c using(roomid)
group by
a.personID
;