1 Reply Latest reply: May 14, 2012 10:12 PM by Oleg Troyansky RSS

    Help with 'where not exists'

    Matthew Green

      I have a query that runs like a dog, mainly due to a where condition similar to this

       

      AND c.uidcot not in (select z.uidcot FROM table1 Inner join table2 inner join table 3 where a=b and b=c and d=f)  you get the point

       

      So my plan was to move that nested select statement to it's own LOAD, and then in another LOAD bring in the core data, but place a WHERE NOT EXISTS (uidcot).

       

      ExcludeUID:

      LOAD uidcot;

      SQL SELECT....;

       

      Data:

      LOAD

      uidcot,

      field1,

      field2

      WHERE NOT EXISTS (uidcot);

      SQL SELECT .....;

       

      Now this worked, but with a hitch..  What I only just learnt is that the WHERE NOT EXISTS checks Data as well.. It makes sense, but it somehow never occured to me that it would do that.

       

      So, what I need to do is force the WHERE NOT EXISTS to only compare against the ExcludeUID table..

       

      How is that done..

        • Help with 'where not exists'
          Oleg Troyansky

          you need to use the second form of exists - comparing an expression to a field. Rename the other field, and compare one field to another:

           

           

          ExcludeUID:

          LOAD uidcot as uidcot_excl;

          SQL SELECT....;

           

          Data:

          LOAD

          uidcot,

          field1,

          field2

          WHERE NOT EXISTS (uidcot_excl, uidcot);

          SQL SELECT .....;