ORACLE SQL JOINS -

i have 2 tables:

table1:

id  name    values 1   john    ab 2   marry   cd 3   sreya   yg 

table2:

pid country     values 45  india       jkabhj 46  usa         yurrrcd 47  uk          yghjjkljl 

output

name    values  country john    ab      india marry   cd      usa sreya   yg      uk 

i want join these 2 tables on common columns values, other table columns contain data. how overcome this?

table2 column "values" contains data matching table1 "values"

values ab cd yg  values jkabhj yurrrcd yghjjkljl 

you can use like operator in query matching values in table1 , table2.

for query:

with table1 (     select  1 id, 'john' name, 'ab' value dual union     select  2 id, 'marry' name, 'cd' value dual union     select  3 id, 'sreya' name, 'yg' value dual ), table2 (     select  45 id, 'india' country, 'jkabhj' value dual union     select  46 id, 'usa' country, 'yurrrcd' value dual union     select  47 id, 'uk' country, 'yghjjkljl' value dual ) select a.name, a.value, b.country table1      join table2 b on b.value '%'||a.value||'%'; 

output:

name    value   country john    ab      india marry   cd      usa sreya   yg      uk 

but recommend change structure make more efficient. example, adding new table table2_values column id referenced table2.id , split values:

with table1 (     select  1 id, 'john' name, 'ab' value dual union     select  2 id, 'marry' name, 'cd' value dual union     select  3 id, 'sreya' name, 'yg' value dual ), table2 (     select  45 id, 'india' country dual union     select  46 id, 'usa' country dual union     select  47 id, 'uk' country dual ), table2_values (     select  45 id, 'jk' value dual union     select  45 id, 'ab' value dual union     select  45 id, 'hj' value dual union     select  46 id, 'yu' value dual union     select  46 id, 'rrr' value dual union     select  46 id, 'cd' value dual union     select  47 id, 'yg' value dual union     select  47 id, 'hj' value dual ) select a.name, a.value, c.country table1      join table2_values b on b.value = a.value      join table2 c on c.id = b.id;