SQL WHERE using LIKE and comparing to a field

Question

I have this scenario:

I want to check for particular words, and if they match a term, I will have to update the content of that page and link it to the term. But for now I am focusing on getting the content pages which have a part of the content the same as a particular term.

This is an idea of what I need to do, but it is not working since the subquery returns more than one field.

I want to find WHERE m.module_content is LIKE any of the terms I have, but it should check with them all.

SELECT m.module_termid, t.term_name, m.module_name, m.module_content
FROM modules m
JOIN terms t ON m.module_termid = t.term_id
WHERE m.module_content LIKE  '%' || (SELECT term_name FROM terms) ||  '%'

module_content has text in html format, so eventually all I would need to do is, if it matches a term and it is not yet links, I will add a link to that particular term.

What is the best option to do here? (I am using mysql btw)

To give you an example of what the expected result is:

Terms: id: 1, name: hello Modules: id: 1, content: < p > Hello World < /p >

I would like that modules with id 1 is brought up, since it contains content which somewhere has the term name "hello"

Updated:

Tried Pablo's solution but this is what happens:

"Ray Davis" has nothing to do with the term "Float" for example, so that should not have appeared.

Answer 1

I think you just need to change your JOIN condition to something like:

SELECT m.module_termid, t.term_name, m.module_name, m.module_content
  FROM modules m
  JOIN terms t ON (m.module_content LIKE  '%' || t.term_name ||  '%')

Having said that, this could be potentially very inefficient. Consider using a FULL TEXT INDEX INSTEAD for this operation.

Answer 2

After a bit of research, my solution would look like this:

SELECT m.module_termid, t.term_name, m.module_name, m.module_content
  FROM modules m
  INNER JOIN terms t ON m.module_termid = t.term_id
  WHERE m.module_content LIKE CONCAT('%', TRIM(t.term_name), '%')

edit: Regarding Paul Morgans comment, I replaced CONCAT('%', t.term_name, '%') with CONCAT('%', TRIM(t.term_name), '%') so that all the whitespaces in t.term_name are stripped off. If you need the whitespaces in t.term_name, just remove the TRIM call and use the old version (CONCAT('%', t.term_name, '%'))