MySQL: JOIN two tables on LIKE

Question

I have a table with a column containing text, and I want to select all of the tables where the text from one of the rows in a second table appears in that row. Here is a pseudo-query:

SELECT title FROM recipes AS r 
   JOIN ingredients AS i 
   ON r.ingredients LIKE '%' + i.name + '%';

The above query does not work, however. How do I do this correctly?

Answer 1

You can't concatenate strings with the + operator (it's for arithmetic only). Use concat('%',i.name,'%') instead.

Answer 2

SELECT title
FROM recipes r JOIN ingredients i ON r.ingredients LIKE concat('%', i.name, '%')

MySQL is weird, and makes you use the concat operator to concatenate strings together. Most others use ||