Yonbantai Yonbantai - 1 month ago 5
SQL Question

SQL partially join

I have problem with SQL join.

I have two tables. table1: name, sth. table2:name,sth.

What I would like to do is to find rows in table2, whose name contains table1.name. e.g such rows should be selected:

table1.name = aa. table2.name = aab

I wrote a query:

select *
from table1
inner join table2 on table2.name like '%'+table1.name+'%'

It returns the error

[42000]: Error while compiling statement: FAILED: SemanticException [Error 10017]: Line 2:3 Both left and right aliases encountered in JOIN ''%''

Can anyone help me on this? Thanks very much


You can try this

select * 
from table1 t1 
where exists (select 1 
              from table2 t2 
              where t2.name like '%'+t1.name+'%')