4 Replies Latest reply on Sep 9, 2013 3:50 PM by nicholas.vasilius

    How do you select a specific record from a table with a one-to-many relationship?

    nicholas.vasilius

      I am connecting to a SQL database with multiple tables. One of the joins has a "one-to-many" relationship to the other tables. However, I don't want to duplicate the data in the "one" table based on it's connection to the many table. For example:

       

      Here is the data in the "One" table:

      ApplicationIDNameAmountApplicationDate

      1

      Joe$101/1/2013
      2John$201/4/2013
      3Jane$301/6/2013
      4Adam$401/10/2013

       

      Here is the data in the "Many" table:

      DecisionIDApplicationIDDecisionDecisionDate
      11Recommended1/1/2013
      21Approved1/2/2013
      32Recommended1/5/2013
      42Declined1/10/2013
      53Recommended1/8/2013
      64Withdrawn1/15/2013

       

      These tables are joined on "ApplicationID", so the resulting data would look like this:

      ApplicationIDDecisionIDDecisionAmountApplicationDateNameDecisionDate
      11Recommended$101/1/2013Joe1/1/2013
      12Approved$101/1/2013Joe1/2/2013
      23Recommended$201/4/2013John1/5/2013
      24Declined$201/4/2013John1/10/2013
      35Recommended$301/6/2013Jane1/8/2013
      46Withdrawn$401/10/2013Adam1/15/2013

       

      Is there anyway to use filtering to select the "most recent" decision, so that the resulting data would look like this:

      ApplicationIDDecisionIDDecisionAmountApplicationDateNameDecisionDate
      12Approved$101/1/2013Joe1/2/2013
      24Declined$201/4/2013John1/10/2013
      35Recommended$301/6/2013Jane1/8/2013
      46Withdrawn$401/10/2013Adam1/15/2013

       

       

      Thank you in advance!