CODEWITHSUNDEEP
mysqlsql
user38858
user38858

Reputation: 316

List of particular events on particular items which took place after other events

I have a table similar to the following:

+--------+----------+--------+--------+
| itemID | actionID |  date  | userID |
+--------+----------+--------+--------+
|      3 | a        | 20-Apr | alice  |
|      4 | a        | 1-Jun  | bob    |
|      1 | b        | 2-Jun  | sam    |
|      5 | c        | 28-May | sally  |
|      4 | c        | 9-Apr  | sally  |
|      3 | b        | 24-Apr | bob    |
|      6 | b        | 29-Apr | bob    |
|      7 | a        | 15-May | alice  |
+--------+----------+--------+--------+

The scenario is like this:

I need to know whether UserID X has taken actionID b on itemID Y after the last time any user has taken actionID a on that itemId Y.

In the non-abstract, this is for a password manager.

Some user takes modifies a password actionID a on itemId Y at date Z, and I need to know whether another user userID A has viewed actionId b after the modify date Z.

Also, if a user modifies a password, I need to assume they also are viewing the password.

Upvotes: 0

Views: 49

Answers (1)

Vulcronos
Vulcronos

Reputation: 3456

Try this:

select *
from myTable t1
where userId = X and actionId = b and itemId = y
and t1.date > (select max(date) from myTable t2 where actionId = a and itemId = Y)

You may want to handle the case when no one has done action a on item Y yet. Right now it will return nothing if no one has done that action yet.

Upvotes: 2

Related Questions