How to not likes many items in MySQL?

Question

It's not gentlemanly to not like many items. But I need a query that exclude many items (not in) using a list of many items using not like.

| source |
|========|
| danny  |
| ram    |
| nach   |
| boom   |
| trach  |
| banana |

| key_exclude    |
|================|
| danny          |
| ram            |

| like_exclude   |
|================|
| bo             |
| tr             |

The expected results:

| result |
|========|
| banana |
| nach   |

I want to something like that:

select * from source where key not in (select key from key_exclude.key) 
  and key not like in(like_exclude.key)

But it is doesnt work

I can do:

select * from source where key not in (select key from key_exclude.key) 
  and key not like '%bo%' and key not like '%tr%'

The only problem is that the 'like_exclue' may contain hundreds of records.

I have read this: http://stackoverflow.com/questions/1127088/mysql-like-in but the answers them told him to add all the items to the `query`, but in this case it impossibile. I want the `like keys` will be in a table — Aminadav Glickshtein, Mar 10 '16 at 07:48
Did you try this one: http://stackoverflow.com/questions/2388323/adding-the-value-of-a-column-into-a-like-statement ? — HerpaMoTeH, Mar 10 '16 at 07:52
I see many answers (and some of them not tested). If someone can provide SQL Fiddle, will be much appreciate — Aminadav Glickshtein, Mar 10 '16 at 10:12

score 2 · Accepted Answer · edited Mar 10 '16 at 15:48

Here is a solution query using subqueries:

select * 
from source AS s
where
    s.`key` not in( select k.`key` from key_exclude AS k ) AND
    NOT EXISTS( select 1 from like_exclude l WHERE s.`key` LIKE CONCAT('%', l.`key`, '%'));

Below is a full demo with data verified, also put on SQLFiddle by OP http://sqlfiddle.com/#!9/22fe8a/1/0

SQL:

-- Data
create table source(`key` char(20));
insert into source values
( 'danny' ),
( 'ram' ),
( 'nach' ),
( 'boom' ),
( 'trach' ),
( 'banana' );

create table key_exclude( `key` char(20));
insert into key_exclude values
( 'danny' ),
( 'ram' );

create table like_exclude( `key` char(20) );
insert into like_exclude values
( 'bo' ),
( 'tr' );

-- SQL Needed
select * 
from source AS s
where
    s.`key` not in( select k.`key` from key_exclude AS k ) AND
    NOT EXISTS( select 1 from like_exclude l WHERE s.`key` LIKE CONCAT('%', l.`key`, '%'));

Output:

mysql> select *
    -> from source AS s
    -> where
    -> s.`key` not in( select k.`key` from key_exclude AS k ) AND
    ->     NOT EXISTS( select 1 from like_exclude l WHERE s.`key` LIKE CONCAT('%', l.`key`, '%'));
+--------+
| key    |
+--------+
| nach   |
| banana |
+--------+
2 rows in set (0.00 sec)

Live Sample - SQL Fiddle: http://sqlfiddle.com/#!9/22fe8a/1/0

Is that MySql? `ALL` in combination with `not like`? Never seen it.. and doesn't seem to work — Poul Kruijt, Mar 10 '16 at 07:56
I am sorry, I didn't make myself clear. I forgot to add, in combination with `not like`. — Poul Kruijt, Mar 10 '16 at 07:58
Awesome man! It's really working. Look here for a live example: http://sqlfiddle.com/#!9/22fe8a/1/0 — Aminadav Glickshtein, Mar 10 '16 at 15:27

Poul Kruijt · Answer 2 · 2016-03-10T10:43:49.623

2

Let me first say that it is indeed very ungentlemanly not to like so many things. But sometimes you just have to...

Anyways..one way to do it is to use RLIKE in combination with GROUP_CONCAT instead of LIKE. That would result in something like this. This will probably go 'wrong' if there are special regexp characters inside the like_exclude table. What this would do performance wise is up to you, and with a lot of values in the table you might have to alter the group_concat_max_len value :

SQLFiddle

SELECT 
   * 
FROM 
   source as s
WHERE 
   s.key NOT IN (SELECT ke.key FROM key_exclude as ke) 
   AND
   s.key NOT RLIKE (SELECT GROUP_CONCAT(le.key SEPARATOR '|') FROM like_exclude as le);

edited Mar 10 '16 at 10:43

answered Mar 10 '16 at 10:11

Poul Kruijt

69,713
12
145
149

Thanks. Why did you use `group_concat` and `rlike` here? – Aminadav Glickshtein Mar 10 '16 at 10:15
@Aminadav To create a regexpression with the | (or) seperator. Trust me, it works :D – Poul Kruijt Mar 10 '16 at 10:19
Not working,group_concat not group when there are 100 rows – Aminadav Glickshtein Mar 10 '16 at 13:26
@Aminadav That's why i said you might have to alter the `group_concat_max_len`. To test you could try: `SET @@group_concat_max_len = 16384;` – Poul Kruijt Mar 10 '16 at 13:28
Didn't success to change this variable on cloud SQL by Google. By thank's anyway. – Aminadav Glickshtein Mar 10 '16 at 15:21
@Aminadav Google cloud sql does have the option to change this value though. Check their documentation [https://cloud.google.com/sql/docs/mysql-flags#config](https://cloud.google.com/sql/docs/mysql-flags#config) – Poul Kruijt Mar 10 '16 at 15:26
You right, but it is not working. Right after setting it, it stays as 1024. I'm going to report this bug: http://snag.gy/U5Hfv.jpg – Aminadav Glickshtein Mar 10 '16 at 15:29
Unfortunately! According to the documentation you should change session variables in the `MySQL Flags section`. I am no user of cloud sql, so i do not know what they mean by that :). Good luck with it – Poul Kruijt Mar 10 '16 at 15:34
You right. I found this flag, when editing an instance here: http://snag.gy/18uJd.jpg – Aminadav Glickshtein Mar 10 '16 at 15:42

sagi · Answer 3 · 2016-03-10T08:24:04.447

1

You can use another not in to exclude the results that do answer the LIKE condition

select * from source
where key not in (select key from key_exclude.key) 
and key not in(select key from source
               where key LIKE (select concat('%',key,'%') from like_exclude.key)

But your query seems a little weird, you can't select from a column so I suspect you have to replace

 key_exclude.key -> key_exclude

And the other table too

like_exclude.key -> like_exclude

edited Mar 10 '16 at 08:24

answered Mar 10 '16 at 07:55

sagi

40,026
6
59
84

MySql don't have `minus` I believe – Aminadav Glickshtein Mar 10 '16 at 08:22
@Aminadav You're right my bad, I changed it to not in – sagi Mar 10 '16 at 08:30
Like. It's should work, but the another answer I think give much more performance that this – Aminadav Glickshtein Mar 10 '16 at 10:14
What ever suits you the best @Aminadav – sagi Mar 10 '16 at 10:22

How to not likes many items in MySQL?

3 Answers3