MySQL - NOT IN LIKE

Question

How can I, in mysql, check if a value is inside a number of fields in another table?

Something like

SELECT * FROM table WHERE concat('%',value,'%') NOT LIKE IN(SELECT field FROM anothertable)

But I don't think that's quite right, is it?

Answer 1

No, not quite.

SELECT * FROM table WHERE NOT EXISTS (
    SELECT * from anothertable WHERE field LIKE CONCAT('%',value,'%')
)

will probably do it. Assuming that value is a column on table, and field is the corresponding column on anothertable which may or may not contain value as a substring.

Be warned, though -- this is going to be a very slow query, if anothertable contains many rows. I don't think there's an index that can help you. MySQL will have to to a string-comparing table scan of anothertable for every row in table.

Answer 2

The following query should do it.

SELECT DISTINCT t.* 
FROM   table t, 
       anothertable a 
WHERE  a.field NOT LIKE Concat('%', t.`value`, '%'); 

Answer 3

If I understand your question correctly (assuming you wish to find the value from table in 2 fields (field1 and field2) in "anothertable"):

SELECT * 
FROM table t 
WHERE EXISTS (SELECT Count(*) FROM anothertable WHERE field1 LIKE concat('%',t,value,'%') OR field2 LIKE concat('%',t,value,'%')