Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - NOT IN LIKE

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?

like image 520
James T Avatar asked Oct 08 '22 13:10

James T


2 Answers

The following query should do it.

SELECT DISTINCT t.* 
FROM   table t, 
       anothertable a 
WHERE  a.field NOT LIKE Concat('%', t.`value`, '%'); 
like image 55
Shiplu Mokaddim Avatar answered Oct 12 '22 11:10

Shiplu Mokaddim


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.

like image 38
Ian Clelland Avatar answered Oct 12 '22 12:10

Ian Clelland