How to calculate the values of one column taking into account the values from another table

user_14876572 0
Viewed 43

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

id_user id_role departure_date
1 1 2022-05-05
1 2 2022-06-18
2 1 2022-04-12

user table

id_user name
1 George
2 John
3 Alex

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

user table

id_user name status
1 George 1
2 John 0
3 Alex 0

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:

id_user name status
1 George 1
2 John 0

Thank you in advance

exists mysql sql database
1 Answers

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;
user_104988280
Related