mysql join query using like ?

Question

Hey

I have been trying to get this working for quite a while now but it just doesn't seem to work, maybe it is is not even possible, what i am wanting to do is to perform a mysql join query using like, such as this example i found...

SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.col LIKE '%' + Table2.col + '%'

but it just doesn't seem to work at all, any help that can be given would be brilliant, thanks !

Answer 1

How about this instead:

SELECT * FROM Table1, Table2 WHERE Table1.col LIKE '%'+Table2.col+'%';

Since you're selecting everything from both tables anyway, this would probably get you there.

Answer 2

Try

SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.col LIKE CONCAT('%', Table2.col, '%')

MySQL does string concatenation differently from other databases, so in case you want to port your app, you need to have an alternate version where || is used as concatenation operator, as mentioned by Michael in another answer. This operator doesn't work in MySQL though, since it means or.

Answer 3

I would expect the performance of this join to be terribly bad, i hope you table is small and your performance requirements are low!