CODEWITHSUNDEEP
mysqlsql-likein-operatornot-operator
James T
James T

Reputation: 3320

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?

Upvotes: 5

Views: 9331

Answers (3)

ron tornambe
ron tornambe

Reputation: 10780

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,'%')

Upvotes: 0

Ian Clelland
Ian Clelland

Reputation: 44192

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.

Upvotes: 2

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57690

The following query should do it.

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

Upvotes: 2

Related Questions