mysql where like in,MYSQL在“ WHERE”子句中使用“ LIKE”在子查询中搜索

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?

解决方案

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)