I have three SQL database tables as shown bellow:
In SQL , i want to retrieve the first three persons (marked with a tick) because they have activities that follow each other (FR, ISRO, VIS, REC) in sequence order dates. The last two persons (4 and 5) must not appear because 4 starts with DC and 5 has DE as the second last activity instead of VIS.
Please note that there can be other activities in between the history of these persons. But the idea is that if there is FR and after that it is followed by ISRO and after that by VIS and after that by REC, then this person must show up. NB (even if there might be other activities between them.
Please help with the code.
The pseudocode is:
FROM Person LeftJoin History On Person.person_id = History.person_id
Where ActivityName = FR followed-by ISRO followed-by VIS followed-by REC
even if there can be other activities between them.