All,

I am trying to select from a list of ids where the ids are not in a
table. So far all I've come up with is adding all the ids to a
temporary table and then selecting from one where not in the other.

Is there an easy (no table creation) way to do this?

eg. (in pseudo code)

Table Test
id varchar (2)
{'aa', 'cc', 'dd')

List testList
('aa', 'bb')

I want to be able to see 'bb' is in list testList but not in table
Test.

appreciate any help
-dmclaude

"dmclaude" <dmclaude (AT) yahoo (DOT) com> wrote

Look up WHERE...IN

dmclaude wrote:

"dmclaude" <dmclaude (AT) yahoo (DOT) com> wrote

you've answered your own question :


you'll see that you've described your problem with the operator that you
need to use

also, temporary tables are infrequently used in solving problems -- they are
somewhat of a last resort. usually there is an efficient and relatively
straight-forward way to solve the problem using SQL syntax, often using a
subquery (another hint for your situation)

++ mcs

I think a PL /Sql table should help u to store the records .... if you
want to make the data in it to be persistant you could always declare
it in a package !!!

Then doin a not in should solve ur problem. But i think a join would be
a better option
cause a not in will do a full table scan.