I need a query that allows me to calculate the values of a column taking into account the values in another table, more precisely, I need to calculate if the users are still active(if a user has all expired roles, then he is inactive), taking into account the departure_date the second table

user_role table

user table

I want to return this table, where 1 is active and 0 is inactive:

user table

At this point, I've made a query, which returns all my inactive users and active users that have one or more assigned roles. I want to get all users including those who do not have an assigned role (like Alex, in my example)

SELECT user_management.user.*,
       if(count(CASE when current_date() > departure_date_organization 
                     then 1 END) = count(*),0,1) as status
FROM user_management.user, 
     user_organization_role 
WHERE user.id_user = user_management.user_organization_role.id_user 
GROUP BY user.id_user;

Using my query, I got this result, that not contain Alex:

Thank you in advance

CodePudding user response：

You don't need joins and aggregation.
Use EXISTS:

SELECT u.*,
       EXISTS (
         SELECT 1 
         FROM user_management.user_organization_role r 
         WHERE r.id_user = u.id_user AND r.departure_date_organization > CURRENT_DATE
       ) status
FROM user_management.user u;