MySQL: Add a condition on a subquery from a joint table

debugcn Published at Dev

mr5

Get all the rooms which this "user" is part of and list each room details including its

id (roomId)
name (roomName)
total active users of this room (activeUsers)
total users included in the room (totalUsers)

Provided with the table structure below:

Table:users
+---------+-------------------------+
| id(int) |  active(bool/tiny int)  |
+---------+-------------------------+

Table:rooms
+---------+----------------------+
| id(int) |  name(varchar(255))  |
+---------+----------------------+

Table:room_members
+-------------+---------------+
| userId(int) |  roomId(int)  |
+-------------+---------------+

I'm kind of lost with my SQL so far:

SELECT
    rooms.id,
    rooms.name,
    (SELECT SUM(users.active) FROM users WHERE room_members.roomId = rooms.id) activeUsers,
    (SELECT COUNT(users.id) FROM users WHERE room_members.roomId = rooms.id) totalUsers
FROM room_members
INNER JOIN rooms
ON room_members.roomId = rooms.id
INNER JOIN users
ON room_members.userId = users.id
WHERE room_members.userId = 1

Basing from this data:

I can conclude that I've got the wrong results.

I guess I need some help from you guys.

scaisEdge

You could use a dynamic table with group by ( and a join between user and room_members)

SELECT
  rooms.id,
  rooms.name,
  t1.activeUsers,
  t1.totalUsers
FROM room_members 
INNER JOIN rooms  ON room_members.roomId = rooms.id
INNER JOIN users  ON room_members.userId = users.id
INNER JOIN  (  
                SELECT roomId, SUM(users.active)  as activeUsers, COUNT(users.id) as totalUsers
                FROM users 
                INNER JOIN room_members on users.id = room_members.userId
                group by roomId
            ) t1 on t1.roomID = rooms.id
WHERE room_members.userId = 1

Collected from the Internet

Please contact [email protected] to delete if infringement.