2 Replies Latest reply: Jun 12, 2013 6:49 AM by Jindrich Kratky RSS

    Joining tables with condition

      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