2 Replies Latest reply: Dec 30, 2009 2:36 PM by Justinas Petkevicius RSS

    Need to join two tables like in sql

    Blessy Jeyantha

      Hi,

      Is it possible like in sql ,can we load one table based on condition (field from another table comparison to this table field)

       

      example:

      table1::

      load f1,f2 from ...qvd;

      table2::

      load g1,g2 from ...qvd;

      table3:

      load f1,f2 from table1 where table1.f1=left(table2.g2,2) ;

        • Need to join two tables like in sql

          Hi,

          You can do it but not as in SQL, there are other methods of doing this e.g using joins. Please see an example below:

           


          table1:
          load * inline [
          f1,f2
          AP,10
          JP,20
          ];
          test:
          load * inline [
          g1,g2
          APAC,10
          ];
          table2:
          load
          *,
          left(g1,2) as g3
          resident test;
          drop table test;
          table3:
          load
          g3 as f1
          resident table2;
          inner join (table3)
          load f1,
          f2
          resident table1;
          drop table table1;
          drop table table2;


          • Need to join two tables like in sql
            Justinas Petkevicius

            Hi, Blessy

            I think You must have the same values as a key to compare two different tables (in Your case left(g2,2) should be a value).

            This way the solution could be:

             


            table1:
            load * inline
            [f1, f2
            a1, 2
            b1, 3
            c1, 1
            ];
            table2.temp:
            load * inline
            [g1, g2
            d1, a1aa
            d2, b1aa
            d3, d1aa
            ];
            table2:
            load g1,
            g2,
            left(g2,2) as g3
            resident table2.temp;
            drop table table2.temp;
            table3:
            load f1 as t1,
            f2 as t2
            resident table1
            where exists(g3, f1);