Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    6

    Question Unanswered: Subquery Counts without using a subquery?

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: Subquery Counts without using a subquery?

    Code:
    select a.personid, a.personname, c.roomname, count(b.personid) from people a, people b, rooms c
    where a.roomid = c.roomid and a.roomid = b.roomid
    group by a.personid, a.personname, c.roomname;

  3. #3
    Join Date
    Jun 2003
    Posts
    6

    Re: Subquery Counts without using a subquery?

    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
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •