How to get an element in the list which does not have the desired value?

Question

MySQL

There is a table with data:
id | value | list

The listdata of the form is stored in 111 222 333 444(I haven’t figured out how best to store them, the data listwill constantly increase, up to thousands in one line, it seems to me that this is not quite the right way ...)

The data in the table:

id | value | list
-------------------------
1  |  ...  | 111 222 333
2  |  ...  | 333 444
3  |  ...  | 555 444 333
4  |  ...  | 444 111 333
5  |  ...  | 111 222

Question: how to get one row with the smallest id(i.e. id == 2) where in listno 111?

I tried this, but because of the% it does not display correctly.

SELECT * FROM table WHERE list NOT LIKE '%111%' OR NOT NULL ORDER BY id LIMIT 1

If I explained it poorly, then in the list I store the unique id of the users who viewed the content, so I mark it so that it is no longer shown to the user.

Answer 1

SELECT * FROM table 
  WHERE CONCAT(' ', list, ' ') NOT LIKE '% 111 %'
  ORDER BY id LIMIT 1

If you use a comma as a separator, for example, the CONCAT_WS function , that is, FIND_IN_SET .

Answer 2

You need to separate list into a separate table.
And set the conditions in the sample as aggregation functions (google SQL HAVING)

infoblocks           showed             users
                                
id | value      info_id | user_id     id  | name 
-----------     ------- | --------    --- | --------
1  |  ...       111     | 333         111 | 333
2  |  ...       333     | 222         333 | 
3  |  ...       555     | 333         555 | 333
4  |  ...       444     | 333         444 | 333
5  |  ...       111     | 555         111 |