select from where id count in a sub query-CodePudding

i have a table named Groups that have an id and maxUsers.

i also have a Scores table that have id, groupId and userId

i want to find a row in groups that have less than maxUsers rows with the same group id in scores and that non of the rows have the same userid as ? input

so i assumed when ever i put the column id in the bottom query it will reffer to the id in the current groups row, i dont think that this is happening right now, is it possible to make it work like i intend? and is there a way to make it work more efficiently?

select id from Groups where 
(select count(1) from Scores where groupId = id) < maxUsers and 
(? not in (select userId from Scores where groupId = id)) limit 1;

CodePudding user response：

The problem is that id in the subquery is interpreted as Scores.id, not Groups.id, you need to qualify it with the table name to override this.

But a better way is to use a JOIN with a grouped subquery.

You can use the LEFT JOIN ... NULL pattern in the subquery to filter out the groups that include the specified userId.

SELECT g.id
FROM Groups as g
JOIN (
    SELECT groupId, COUNT(*) AS grount
    FROM Scores AS s1
    LEFT JOIN (
        SELECT DISTINCT groupId
        FROM Scores
        WHERE userId = ?
    ) AS s2 ON s1.groupId = s2.groupId
    WHERE s2.groupId IS NULL
    GROUP BY groupId
) AS s ON g.id = s.groupId AND s.count < g.maxUsers
LIMIT 1