1 Reply Latest reply on Apr 20, 2009 12:04 PM by philmodjunk

    self-join?  or other technique?



      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.





        • 1. Re: self-join?  or other technique?

          A self join should work for you (and self joins are a good way to maintain a properly normalized structure to your tables). I can't tell from your description why it's not working. You can encounter problems with the data in the key fields (do they really match like you expect), in how the details of the relationship are set up or in how you've structured your layout. I can't tell if any of these issues might be the cause of your problem without knowing more about the tables/relationships and layouts involved.


          There is an alternative for your specific case. Are you able to set up a portal that lists all the itinerary records for one traveler? If so, you can specify a sort order for that relationship (or a copy of that relationship) that sorts by your datetime field to make it the first row in your portal. Placing that single related field on your layout without using a portal may well provide what you need.