mr5 mr5 - 1 month ago 10
MySQL Question

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




  • 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:

sample users data

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

capture of mysql query

I guess I need some help from you guys.

Answer

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