Return-path: Envelope-to: oracle-l@orafaq.net Delivery-date: Tue, 24 Sep 2002 01:32:58 +0800 Received: from newsfeed.cts.com ([209.68.248.164]) by jean.onefuse.com with esmtp (Exim 3.36 #1) id 17tX4r-0007bZ-00 for oracle-l@orafaq.net; Tue, 24 Sep 2002 01:32:57 +0800 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id KAA72304; Mon, 23 Sep 2002 10:33:04 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004D6D5E; Mon, 23 Sep 2002 09:58:34 -0800 Message-ID: Date: Mon, 23 Sep 2002 09:58:34 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Nicoll, Iain \(Calanais\)" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Nicoll, Iain \(Calanais\)" Subject: RE: SQL question Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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@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@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@calanais.com 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@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).