Can you SELECT WHERE something's LIKE and IN at the same time?

Question

I need to write what I'd like to call a Valley Girl-query. I need to SELECT something that's LIKE IN - something like this:

SELECT * FROM Table1 WHERE Name LIKE IN ( SELECT Name FROM Table2 )

The reason for this is I've got a table full of company names, but they're not exactly the same - f.ex. in Table1 it might say "Chrysler Group", while in Table2 it might just say "Chrysler".

Is there any easy way to do this?

Answer 1

I think:

SELECT * FROM table1 WHERE EXISTS (SELECT Name FROM Table2 WHERE Table1.Name LIKE Name)

Answer 2

select
  *
from
  Table1 t1
    inner join Table2 t2 on (t1.name like t2.name + '%')

or without '%' sign if you want :)

Answer 3

Hi,

Here's one way you could do it:

SELECT t1.*
FROM Table1 t1
    JOIN Table2 t2 ON t1.Name LIKE t2.Name + '%'

Answer 4

If you are trying to create a list of close matches then the SOUNDEX function can be helpful when there may be questionable spelling.

SELECT T1.* 
  FROM Table1 T1    
  JOIN Table2 T2 ON SOUNDEX(T1.Name) = SOUNDEX(T2.Name)