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

Question

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?

Answer 1

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)