1 Reply Latest reply: Oct 31, 2010 9:48 AM by Michael Solomovich RSS

    Question about left join and where

    mauich123

      Hi

      I've searched around a bit on the forums but haven't found anything really relevant, although I might have missed it :)

      Anyway, I have 2 tables which i load from QVD files. Both tables have a field, lets call it Field1 which has many identical values in both tables. The first table also has a field, we can call it Field2.

      What I want to do is to join these to tables and ONLY load rows from Table2 where Field2 in Table1 is greater than a specific value.

      I've tried to do it like this:

      Load
      Field1,Field2
      FROM Table1.qvd;

      Left join Load
      Field1, Field3
      FROM Table2.qvd where Field2>10;

      But this doesnt work (of course). How do I accomplish this?

       

        • Question about left join and where
          Michael Solomovich

          One way to accomplish this is to join both tables as is, and after that run another load to eliminate records:


          table_tmp:
          Load
          Field1,Field2
          FROM Table1.qvd;
          Left join (table_tmp) Load
          Field1, Field3
          FROM Table2.qvd;
          NOCONCATENATE
          table:
          LOAD
          Field1,Field2,
          if(Field2>10, Field3) as Field3
          RESIDENT table_tmp;


          Another way, which probably will by my preference, is to use mapping:


          table:
          Load
          Field1,Field2
          FROM Table1.qvd;
          Map:
          LOAD DISTINCT
          Field1 as A,
          Field2 as B
          RESIDENT table;
          LEFT JOIN (table) LOAD
          Field1,
          FROM Table2.qvd
          where applymap('Map',Field1,0)>10;