CODEWITHSUNDEEP
mysqloracle-database
NaveenBharadwaj
NaveenBharadwaj

Reputation: 1333

How to select records whose value does not match the list of strings from another query in oracle?

I have two unrelated queries. I need to select records from first query whose column value does not contain any of the values returned from another query. Please suggest a way to do this.

Let us say, first query returns these records:

abc_123_xyz
def_456_hij
lmn_opq
rst_uvw

Let us say, second query returns these records:

123
456

The expected result is:

lmn_opq
rst_uvw

I have written a query which matches the result and it works fine. But when I add NOT LIKEcondition, it fails. Please help!

select o.name 
 from (select pattern from PATTERN) p, 
        objects o
 where o.name like '%'||p.pattern||'%';

Assuming PATTERN table returns 123,456, it matches the values abc_123_xyz, def_456_hij correctly. However if I execute above query with NOT LIKE condition, it fails to return only the other two records. Instead it returns all four values. What is wrong here?

Upvotes: 0

Views: 2434

Answers (2)

user5683823
user5683823

Reputation:

The query with NOT LIKE will return all the rows, and it will return the rows that don't match anything TWICE.

You are joining the two tables, which means you are getting an output row for every combination of a row from the first table and a row from the second table. Total 8 rows (4 * 2).

Then just TWO of those rows are rejected by the WHERE filter - one row, the one that contains 123 , is rejected by the condition NOT LIKE ...123... (but it is still include the other time, because it does meet the condition NOT LIKE ...456...).

What you want is a SEMI-JOIN: you want all the rows from one table (counted only one time!) that don't match anything in the second table. In a join like you wrote, if a row from the first table doesn't match one pattern, it will be included in the output, even if it does match another pattern.

What you need is something like this:

select name
from   objects o
where not exists ( select pattern from p where o.name like '%' || pattern || '%')
;

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

you could use a left join and 

  select o.name 
  from  objects o
  left join PATTERN  p on o.name  like '%'||p.pattern||'%'
  where p.pattern is null ;

Upvotes: 0

Related Questions