RE: SQL question

From: Nicoll, Iain \(Calanais\) <iain.nicoll_at_calanais.com>
Date: Mon, 23 Sep 2002 09:58:34 -0800
Message-ID: <F001.004D6D5E.20020923095834@fatcity.com>

If the set of values is not too big and fixed you could do the minus using dual

e.g.

(select 'A'
from dual
union
select 'B'
from dual
union
...
select 'Z'
from dual)
minus
select code
from table

-----Original Message-----
Sent: Monday, September 23, 2002 5:28 PM To: Multiple recipients of list ORACLE-L

Good morning list,

Environment HP-UX 11.0 Oracle 8.1.6

Can anyone help with this SQL.

I can get a result set of values from a table that match a given list of values -

select code
from table
where code in ('A','B','C','D','E')

I can get a result set of values from a table that do not match a given list of values -

select code
from table
where code not in ('A','B','C','D','E')

So far so good.

Now, how do I get the set of values from the list that do NOT have a matching value in the table?

I cannot create any objects in the schema I am working in otherwise I would create a table with the values and do a minus, but I can't figure out how to do it in SQL only.

Thanks in advance, folks.

Steve
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Steven Haas
INET: steven.haas_at_snet.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Nicoll, Iain \(Calanais\)
INET: iain.nicoll_at_calanais.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------