How to select on a filtering clause that is fed a min value?-CodePudding

I'm trying to get the users who do not have a role lower than a given user.

My logic was going like this when it hit the brick wall:

SELECT u.id, MIN(ar.role_id) as min_role, u.name
FROM user u
LEFT JOIN assigned_role ar ON ar.user_id = u.id
HAVING
min_role >=
(
SELECT MIN(ar2.role_id)
FROM assigned_role ar2
WHERE ar2.user_id = 43
)

This returns nothing.

The MIN(ar.role_id) as min_role is not needed in the user projection. I just need it in the filtering clause.

Why is this statement returning only one row ?

SELECT u.id, MIN(ar.role_id), u.name
FROM user u
LEFT JOIN assigned_role ar ON ar.user_id = u.id

I can see this statement returning many rows:

SELECT u.id, u.name
FROM user u

Same for the assigned roles which are many.

CodePudding user response：

Test this:

SELECT u1.id, u1.name
FROM user u1
JOIN assigned_role a1 ON a1.user_id = u1.id
GROUP BY u1.id, u1.name
HAVING MIN(a1.role_id) >= ( SELECT MIN(a2.role_id)
                            FROM user u2
                            JOIN assigned_role a2 ON a2.user_id = u2.id
                            WHERE u2.id = @given_user_id )

Subquery returns minimal role for given user (which is always scalar value, one row with one column, due to implicit GROUP BY). Outer query compares minimal role for each user with minimal role for given user and returns only those rows where current user's minimal role is not below (including given user, of course).