MySQL join query using like?

mysql join sql-like
David picture David · Dec 18, 2009 · Viewed 48.2k times · Source

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

Tor Valamo picture Tor Valamo · Dec 18, 2009

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.

Related questions

how to use a like with a join in sql?

I have 2 tables, say table A and table B and I want to perform a join, but the matching condition has to be where a column from A 'is like' a column from B meaning that anything can come before …

Read More
SQL Join on a column LIKE another column

Possible Duplicate: mysql join query using like? I want to do a join where one column contains a string from another table's column: SELECT a.first_name, b.age FROM names a JOIN ages b ON b.full_name LIKE …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More