I want to get the records from table2 and table1 that table2.id2(string) include table1.id(string),means table1.id is a substring in table2.id2,can this sql work correctly? Thanks!


SELECT table1.abc, table1.id, table2.id2, table2.ddd
FROM table2 INNER JOIN table1 ON table2.id2 like table1.id

I am sure this will work.

SQL> select * from a;

C1 C2
---------- --------------------
AAAA santosh sarkar
AAAB debasish datta
AAAC sujata agarwal
AAAD rina ray

SQL> select * from b
2 ;

C3 C4
---------- --------------------
XXXX santosh
XXXY debasish
XXXZ agarwa

SQL>


select a.*, b.*
from a, b
where substr(a.C2, instr(a.c2,b.c4), length(b.c4)) = b.c4
SQL> /

C1 C2 C3 C4
---------- -------------------- ---------- --------------------
AAAA santosh sarkar XXXX santosh
AAAB debasish datta XXXY debasish
AAAC sujata agarwal XXXZ agarwa

SQL>



Santosh Sarkar

tshm, you almost got the query right! You only forgot to put the wildcards. This will return all rows where table1.id is found in table2.id2.

SELECT table1.abc, table1.id, table2.id2, table2.ddd
FROM table2 INNER JOIN table1 ON table2.id2 like '%'+table1.id+'%'