problem with UNION and not in clause

I need to construct a query in ESQL/C with cursors that fetches all rows that are of record_type=CURRENT and those with record_type=PREVIOUS only if the CURRENT for that doesn't exist. Am having trouble with the "NOT IN" clause. I know if I put a single field before the NOT IN, it works. How do I construct the query so that I can put more than 1 field before it? Basically, I want the subquery in the second select to exclude all the rows I fetched from the first select. I'd like to avoid using temp tables since I'm not sure if I can do that with cursors. tableA schema f1 f2 f3 f4 record_type The key for this table is comprised of f1, f2 and record_type I'm trying to do this, but I can't get this to work in dbaccess. select f1, f2 from tableA where f3=1 and f4=2 and rec_type='CURRENT' union select f1, f2 from tableA where f3=1 and f4=2 and rec_type='PREVIOUS' and f1, f2 not in ( <----- error here!!select from tableA where f1, f2 where f3=1 and f4=2 and rec_type='CURRENT' ) THANKS GREATLY for any help! Sent via Deja.com http://www.deja.com/ Before you buy.