mysql join query using like?

Question

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

SELECT p.products_id, pd.products_name, p.products_model
FROM products_description pd
JOIN products p ON p.products_id = pd.products_id
WHERE pd.products_name LIKE CONCAT( '%', p.products_model, '%' )
LIMIT 0 , 100

First off you have to restrict your request by (p.products_id = pd.products_id) and LIMIT. And look what time it took. After that you can go and make comparison with (WHERE). If you gonna compare directly within JOIN you will put down your db if there are at list 20 thousands items. Beware.)

Answer 3

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.