CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11goracle10g
Ashok Kumar Dabbadi
Ashok Kumar Dabbadi

Reputation: 265

In Clause With Subquery In Oracle

I have two tables say table1 and table2 with below details

create table test1(id number, name varchar2(20)); 
insert into test1 values(11,'micro'); 
insert into test1 values(22,'soft'); 


create table test2(id number, name varchar2(20)); 
insert into test2 values(77,'micro,soft');

1) if I use below query I am getting no rows selectd

select * from test1 t1 where t1.name in ( select ''''||replace(t2.name,',',''',''')||''''  from test2;

2) if I fire subquery alone output I am getting is : 'micro','soft'

select ''''||replace(t2.name,',',''',''')||''''  from test2;

but if I fire query(1) I need the result 

id   name
------------
11 micro
22 soft

can some one please help me to get the same result with query (1).

Upvotes: 0

Views: 406

Answers (2)

Dba
Dba

Reputation: 6649

Use REGEXP_SUBSTR function to get this done.

SELECT * 
FROM   test1 t1 
WHERE  t1.NAME IN(SELECT regexp_substr(t2.NAME, '[^,]+', 1, ROWNUM) 
                  FROM   test2 t2
                  CONNECT BY LEVEL <= LENGTH (regexp_replace (t2.NAME, '[^,]+'))  + 1
                  );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You have a very strange data layout and should probably change it.

You can do what you want with a join or with a correlated subquery using like:

select *
from test1 t1
where exists (select 1
              from test2 t2
              where ','||t2.name||',' like '%,'||t1.name||',%'
             );

You version doesn't work because the expression:

where x in ('a,b,c')

tests where x is equal to the string value 'a,b,c', not whether it is equal to one of the three values.

Upvotes: 3

Related Questions