I have a single large dataset that is related as follows. Each User has a UserID, when they login a SessionID is created, these Sessions then spawn TransactionID's which is a pair consisting of a request and a response.
The basic logic needs to go as follows:
I carry out a search on a particular UserID on the dataset.
I then collect all the SessionID's that are related to this UserID.
Then using this SessionID I collect a list of all TransactionID's that are used during this session.
Finally I lookup all of the TransactionID's so that I can view all requests and responses that the user has carried out.
I have tried a number of methods but cant seem to get this working correctly. Taking note that the SessionID does not appear in every log record. Sometimes a record is referred only by the TransactionID. The dataset is also huge so there is no way of carrying out a transaction statement on all records before I do the search.
As an interim I carried out the following to create an Index table and then run another search against this...and this works. But its clunky. Any ideas?