Reputation: 125
I’m developing an app. I have to do a query where I see the rooms where I’m guest or admin. That's easy. The problem is that I want to get, at the same time, the number of players inside the same room.
SELECT rooms.id, rooms.name, rooms.buyin, rooms.administrator, rooms.creation_date,
rooms.max_players, rooms.min_players, count(room_players.room)
FROM rooms, room_players
WHERE administrator = 1 and room_players.room = rooms.id
I have a table with the rooms and the other (room_players) that is a match between users and rooms. If i delete the count(room_players.room)
and and room_players.room = rooms.id
I have the rooms that I’m admin. But I need the number of players inside the same room too.
Upvotes: 1
Views: 79
Reputation: 1271241
Your query needs a group by
. With the COUNT()
in the select
, it becomes an aggregation query that returns one row -- a summary of all the rows in the table.
SELECT r.id, r.name, r.buyin, r.administrator, r.creation_date,
r.max_players, r.min_players, count(rp.room)
FROM rooms r join
room_players rp
ON rp.room = r.id
WHERE administrator = 1
GROUP BY r.id;
I also fixed the join syntax to be an explicit join and added table aliases to make the query more readable.
EDIT:
I suspect the query you want is something like:
SELECT r.id, r.name, r.buyin, r.administrator, r.creation_date,
r.max_players, r.min_players, count(rp.room)
FROM rooms r join
room_players rp
ON rp.room = r.id
WHERE administrator <> 1
GROUP BY r.id
HAVING sum(user = 1) > 0;
Upvotes: 5