self-join? or other technique?
I have a database table with travel itineraries in it. A traveller can have multiple itinerary records - one for each leg of their trip. I need to identify the last leg of their trip, and one way to do that is to find the one record where two fields have the same value. One field is named ITINERARY_DATE and one is named ARRIVAL_DATE (both are datetime stamps mm/dd/yyyy hh:mm:ss).
I tried setting up a self-join with the table, specifying that the two date fields have to be equal, as well as two other identifiers uniquely identifying the traveller have to be equal as well.
So now I have a Relationship diagram with two table occurences, one for the original table and a second one for this self-join. Something is not set up right, however, as I get all the records for a traveller, not just the one record I want where the two fields discussed above are equal.
And, unfortunately, I have no control over the database design, so I can't fix this mess with a more nomalized database structure that would solve the problem properly.
Also, I am not sure how this self-join will affect other situations where I need to access the travel data independent of the self-join situation discussed here?
Any suggestions would be appreciated.