1 Reply Latest reply: May 7, 2013 12:26 PM by Gysbert Wassenaar RSS

    left join issue

    Sukhwant Matharoo

      hi all,

       

      i have 3 tables and i want to left join but not getting desired results:

       

      Table 1:

      client  value

      a          10

      b          5    

      c          6

       

      table 2:

      client     category

      a               xxx

      c               aaa

       

      table 3:

      client     category

      b               yyy

       

      desired result:

      client     value category

      a               10     xxx

      b               5       yyy

      c               6       aaa

       

      when use left join, result is

      client      value     category

      a               10      xxx

      b               5          -

      c               6        aaa

       

      left join statement used is below:

       

      test:

      load client,

             value

      from table 1

       

      left join

      load client, category from table 2

       

      left join

      load client, category from table 3

       

      final:

      noconcatenate load

      client,

      value,

      category from resident test

      drop table test

       

       

      please advise how to get desired result from the 3 tables using left join or some other way

       

      thanks in advance

        • Re: left join issue
          Gysbert Wassenaar

          You need to concatenate table2 and table3 first and then right join it with table1.

           

          test:

          load client, category from table 2

          load client, category from table 3

           

          right join

          load client,

                 value

          from table 1

           

          final:

          noconcatenate load

          client,

          value,

          category from resident test

          drop table test

           

          The way you do it now won't work since after joining table1 and table2 the resulting table will have two fields and the join on table3 will use both fields for the joining because table3 also has these two fields.