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.idI 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+'%'
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment