MYSQL use 'LIKE' in 'WHERE' clause to search in subquery

mysql, sql, sql-like, subquery, where-clause

Solution

Using a JOIN:

SELECT a.*
  FROM mytable a
  JOIN myothertable b ON a.name LIKE CONCAT('%', b.name, '%')

...but there could be duplicates, if there's more than one match in `myothertable` for a given `mytable` record.

Using EXISTS:

SELECT a.*
  FROM mytable a
 WHERE EXISTS (SELECT NULL 
                 FROM myothertable b 
                WHERE a.name LIKE CONCAT('%', b.name, '%'))

Using Full Text Search `MATCH` (requires `myothertable` is MyISAM)

SELECT a.*
  FROM mytable a
  JOIN myothertable b ON MATCH(a.name) AGAINST (b.name)

Problem

How would you use 'LIKE' to search in a subquery? E.g. i've tried doing this, but doesn't work: ``` SELECT * FROM mytable WHERE name LIKE '% (SELECT name FROM myothertable) %' ``` I have this so far: ``` SELECT * FROM t1 WHERE t1.name IN (SELECT t2.name FROM t2) AND (t1.title IN (SELECT t2.title FROM t2) OR t1.surname IN (SELECT t2.surname FROM t2)) ``` It's working ok as it returns exact matchs, but it doesn't seem to return my other records that are similar, so I would like to also check that: t1.title LIKE '%t2.title%' AND t1.surname LIKE '%t2.surname%' How would i do this?

Original source