combining LIKE with IN operator in MySQL

I want t开发者_如何学运维o do some thing like this:

select * from table1 where name Like in (select name from table2 where id = '%123')

Suppose

select name from table2 where id = '%123' 

Results are: abc123,def123,ghi123,...

And the table1 contains the name fields as AB-abc123-CD,CD-def123-HB,...

How can I do this?

If I understand you correctly:

select * from table1 where name in (select name from table2 where id Like '%123')

OR

select * from table1 inner join table2 on table1.name like '%' || table2.name || '%'
where table2.id = '%123'

Did I understand you question correctly?

This works on SQL Server, I assume it will work on MySQL too, possibly with minor modifications.

select distinct t1.* from table1 t1
inner join table2 t2
on t1.name like '%' + t2.name + '%'
where t2.ShortString like '%123'