2 Replies Latest reply: Jun 12, 2013 6:49 AM by J K RSS

    Joining tables with condition

    J K

      Hello,

       

      I'll show what I need to achieve on example:

       

      Lets have two tables

      Table1:

      LOAD * INLINE [

                Table_Code, Table_Carrier

                'AA', 'E3'

                'BB', 'R7'

                'CC', 'F5'

      ];

       

       

      Type:

      LOAD * INLINE [

           Type_Code, Type_Carrier, Type_Data

           'AA', 'E3', 'AA E3'

           'AA', 'R7', 'AA R7'

           'AA', '', 'general AA'

           'BB', 'E3', 'BB E3'

           'BB', '', 'general BB'

      ];

       

       

      I would like to list all rows from Table1 and join Type_Data from Type table to them. Rules are:

      • Match the same Code (Table_Code with Type_Code)
      • If there is row with the same Carrier in Type table as has the row in Table1, use this one. Otherwise use the one with blank Carrier if exists.

       

      I have found this solution

      J:

      LOAD

                Table_Carrier as Carr,

                Table_Code as code

      Resident

                Table1;

       

       

      left join

      LOAD

                Type_Carrier as Carr,

                Type_Code as code,

                Type_Data

      Resident

                Type;

       

       

      left join

      LOAD

                Type_Code as code,

                Type_Data as Type_Data_G

      Resident

                Type

      WHERE

                IsNull(Type_Carrier) or Type_Carrier = '';

       

       

      F:

      NoConcatenate LOAD

                Carr,

                code,

                if(not IsNull(Type_Data), Type_Data, Type_Data_G) as Type_Data

      Resident

                J;

       

       

      But it doesn't seems 'elegant' to me. Isn't there any better way how to get to the right result? Using VB is probably a way but the user is accessing project via AJAX thin client and I'm not sure if the VB will work in this case.

       

       

       

      Regards,

      Jindra

        • Re: Joining tables with condition
          Gysbert Wassenaar

          TypeMap:

          mapping

          LOAD Type_Code&Type_Carrier as Key, Type_Data INLINE [

               Type_Code, Type_Carrier, Type_Data

               'AA', 'E3', 'AA E3'

               'AA', 'R7', 'AA R7'

               'AA', '', 'general AA'

               'BB', 'E3', 'BB E3'

               'BB', '', 'general BB'

          ];

           

          Table1:

          LOAD *, ApplyMap('TypeMap',Table_Code&Table_Carrier,ApplyMap('TypeMap',Table_Code,'No Value')) as Type_Data INLINE [

                    Table_Code, Table_Carrier

                    'AA', 'E3'

                    'BB', 'R7'

                    'CC', 'F5'

          ];