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:
I can conclude that I've got the wrong results.
I guess I need some help from you guys.
Answer Source
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