I have a many to many relationship like below. A user can change their role, and I want to get all users with their last role equals to role2 (example).
users
user_id | user_name | user_password
1 | user1 | *hashed password*
2 | user2 | *hashed password*
3 | user3 | *hashed password*
roles
role_id | role_name | role_description
1 | role1 | *description*
2 | role2 | *description*
user_roles
user_roles_id | user_id | role_id | created_at
1 | 1 | 1 | 2018-04-10 01:01:01
2 | 2 | 2 | 2018-04-10 01:01:02
3 | 3 | 1 | 2018-04-10 01:01:03
4 | 1 | 2 | 2018-04-12 01:01:01
5 | 1 | 1 | 2018-04-13 01:01:02
6 | 2 | 1 | 2018-04-14 01:01:01
7 | 3 | 2 | 2018-04-14 01:01:02
8 | 2 | 2 | 2018-04-15 01:01:01
9 | 1 | 2 | 2018-04-15 01:01:02
10 | 1 | 1 | 2018-04-16 01:01:01
From those tables, I want to get things like
user_id | role_id
2 | 2
3 | 2
But all this time I got
user_id | role_id
1 | 2
2 | 2
3 | 2
user1
should not be there because its last role was role1
.
So what I'm trying to do is, get users where last role = 2.
Any help would be appreciated! Thanks in advance!
Addition
The results that I wanted is all data from users
table. So it might be like
user_id | user_name | <and all of the rest>
2 | user2 | ...
3 | user3 | ...
So that, the field role_id
I mentioned above is just reference to select the user.
This is one solution. Essentially, you are looking at each user's latest role, and only include it in the output, if the role is 2.
SELECT
ur.user_id, ur.role_id
FROM
user_roles ur
WHERE
ur.created_at = (SELECT MAX(created_at)
FROM user_roles
WHERE user_id = ur.user_id)
AND ur.role_id = 2
GROUP BY
ur.user_id, ur.role_id;
EDIT
Based on the additional information from the comments, the following will return all information from the Users table:
SELECT
u.*
FROM
users u INNER JOIN (
SELECT
ur.user_id
FROM
user_roles ur
WHERE
ur.created_at = (SELECT MAX(created_at)
FROM user_roles
WHERE user_id = ur.user_id)
AND ur.role_id = 2
GROUP BY
ur.user_id, ur.role_id) tmp ON tmp.user_id = u.user_id;