Help with 'where not exists'

[#1]

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..

[Oleg_Troyansky · Accepted solution]

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 .....;