4 Replies Latest reply: Jun 1, 2012 5:36 AM by Reynald Liandra RSS

    Join a table with another table using subset of first table's key

      Hi,

      the title might be confusing but here is the idea:

       

       

      I have 2 tables and I want to join both tables, but....

      here is the case:

       

      table1:

       

      CodeDescription
      101.110.255Desc1
      101.100.202Desc2
      101.200.301Desc3
      202.110.111Desc4
      202.100.200Desc5
      302.300.100Desc6

       

       

      table2:

       

      CodeGroupGroupName
      101.1Current Asset
      101.200Fixed Asset
      202.11Income
      202.10Other Income
      302.3

      Other Income

       

      The expected result is:

       

      CodeDescriptionCodeGroupGroupName
      101.110.255Desc1101.1Current Asset
      101.100.202Desc2101.1Current Asset
      101.200.301Desc3101.200Fixed Asset
      202.110.111Desc4202.11Income
      202.100.200Desc5202.10Other Income
      302.300.100Desc6302.3Other Income

       

      I want to show which code belong to which CodeGroup and GroupName.

      Thanks for your help