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

Question

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.

Answer 1

You cound use a dinamic table with group by ( and a join between user and room_members)

SELECT
  rooms.id,
  rooms.name,
  t1.activeUsers
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 totaleUsers
                FROM users 
                INNER JOIN room_members on users.users.id = room_members.userId
                group by roomId
            ) t1 on t1.roomID = rooms.id
WHERE room_members.userId = 1

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

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

Sign up