Home » SQL & PL/SQL » SQL & PL/SQL » Find unmatched(3 merged)
Find unmatched(3 merged) [message #419276] Thu, 20 August 2009 18:51 Go to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
If I have a list of, say, 10 items (see items 1, 2, 3...below) I want to compare to similar items contained in a table ('my_table' below), how can I get a result that will show which of the ten items in my list were NOT in the table?

select * from my_table where item_id in (
1, 2, 3, 4, 5, 6, 7, 8, 9, 785)

(If my_table above contains only 9 items IDs (1 thru 9), how do I get the "785" to be returned?) I know there are a lot of ways to do comparisons this but isn't there a simple way to accomplish this?

Thx.
Re: Find unmatched [message #419280 is a reply to message #419276] Thu, 20 August 2009 20:13 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

> isn't there a simple way to accomplish this?
if there was a simple way, would you be asking this question?
Re: Find unmatched(3 merged) [message #419296 is a reply to message #419276] Fri, 21 August 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I know there are a lot of ways to do comparisons this but isn't there a simple way to accomplish this?

Post the ways you know and explain why there are not simple.
Also explain what is a "simple" way.

Regards
Michel
Re: Find unmatched(3 merged) [message #419308 is a reply to message #419276] Fri, 21 August 2009 02:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In order to select the values that are not in the table, you first need to create a data source that has your values in it.
For example:
select 1 col_1 from dual union all
select 2 col_1 from dual union all
select 3 col_1 from dual union all
select 4 col_1 from dual union all
select 5 col_1 from dual;


If your source data is a series of increasing values (such as the number 1..10 in your example) you can use a standard row generator:
select level col_1 from dual connect by level <= 10;


Once you've got your values as a data source, you can either outer join to 'my_table' or use a NOT IN subquery:
create table test_048 (col_1 number);

insert into test_048 values (1);
insert into test_048 values (3);
insert into test_048 values (5);

select * 
from (select level col_1 from dual connect by level <= 10)
where col_1 not in (select col_1 from test_048);
Re: Find unmatched(3 merged) [message #419310 is a reply to message #419308] Fri, 21 August 2009 02:11 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this a "simple" way? Wink

Regards
Michel
Previous Topic: Query Parsing
Next Topic: Trigger for two tables
Goto Forum:
  


Current Time: Mon Dec 05 15:10:56 CST 2016

Total time taken to generate the page: 0.11119 seconds