How to select only a specific set that includes some or all of another set in mySQL

Question

I'm trying to extract specific rows from a mySQL table that contains lists of numbers.

I have a single table with 2 columns- id and data. Each row has a sorted, comma separated record of numbers ranging from 1 to 1000. I want to only select records with a partial or full set of specific numbers in it. I've tried using LIKE and IN and also looked at FIND_IN_SET.

t1.id       t1.data
1          2,9,569
2          2,9,991,979
3          9,569,763
4          52,57,569,763,892,897
5          763
6          2,9,10,15,151,569,771,801,888,973

If I'm looking for rows with one or more of the values (2,9,569,763), I don't want to have to write:

SELECT t1.id from t1
WHERE t1.data NOT IN (1,3,4,5,6,7,8,10,11,...........,1000);

to return 3 rows, t1.id = 1,3 and 5.

Is there a simpler way? Something like (in mySQL):

SELECT t1.id from t1
WHERE t1.data "only includes one or more of" (2,9,569,763);

I would explode each row into separate entries keyed on id, and then use an ordinary SELECT statement to find the values. Do you have access to PHP to do the exploding? Otherwise this may help: https://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql — MandyShaw, Feb 18 '19 at 19:54
Corrected my table since I had no rows meeting the "partial or full set" requirement. — DHHJ, Feb 18 '19 at 20:09

score 1 · Answer 1 · answered Feb 18 '19 at 21:33

1

Not simple but..

Count single hits and compare it with the number of all values in the data column. They must be equal.

select id, data
from t1
where (find_in_set(2, data) > 0)
    + (find_in_set(9, data) > 0)
    + (find_in_set(569, data) > 0)
    + (find_in_set(763, data) > 0)
    = char_length(data) - char_length(replace(data, ',', '')) + 1

Demo: https://www.db-fiddle.com/f/oLcrz4vmXRWXqYQhCnZA5z/0

answered Feb 18 '19 at 21:33

Paul Spiegel

30,925
5
44
53

Consider where data is `(2,10,569,763)`. The number of elements may match, but the values don't. So counting the number of elements is not proof that the values are from the correct set. – Bill Karwin Feb 18 '19 at 21:33
@BillKarwin But I only count the hits on the left side. It would be 3 in this case. I included your example in the demo: https://www.db-fiddle.com/f/tS2Da5ZoDRK9oNHtmUvyab/0 – Paul Spiegel Feb 18 '19 at 21:36
Aha! I see. I retract my objection. :) – Bill Karwin Feb 18 '19 at 21:37

Bill Karwin · Accepted Answer · 2019-02-18T21:59:28.393

Paul Spiegel's answer gives the correct result, but it can't be optimized with indexes, because of the use of FIND_IN_SET(). It will always do a table-scan, which will get more and more expensive the more rows you have.

You should take this as a clue that storing lists of numbers as a comma-separated list in a string column is a bad idea when you actually want to do some searches for discrete members of that list.

What you should do instead is store the list as a child table, with one member per row.

CREATE TABLE mydata (
  t1id INT NOT NULL,
  member INT NOT NULL,
  PRIMARY KEY (t1id, member),
  FOREIGN KEY (t1id) REFERENCES t1(id)
);

INSERT INTO mydata VALUES
(1,2),(1,9),(1,569),
(2,2),(2,9),(2,991),(2,979),
(3,9),(3,569),(3,763),
(4,52,(4,57,(4,569,(4,763,(4,892),(4,897),
(5,763),
(6,2),(6,9),(6,10),(6,15),(6,151),(6,569),(6,771),(6,801),(6,888),(6,973);

Now you would join your original table t1 to mydata but exclude the matches to values in your desired list.

mysql> select * from t1 left join mydata on t1.id=mydata.t1id 
    and mydata.member not in (2,9,569,763);
+----+------+--------+
| id | t1id | member |
+----+------+--------+
|  1 | NULL |   NULL |
|  2 |    2 |    979 |
|  2 |    2 |    991 |
|  3 | NULL |   NULL |
|  4 |    4 |     52 |
|  4 |    4 |     57 |
|  4 |    4 |    892 |
|  4 |    4 |    897 |
|  5 | NULL |   NULL |
|  6 |    6 |     10 |
|  6 |    6 |     15 |
|  6 |    6 |    151 |
|  6 |    6 |    771 |
|  6 |    6 |    801 |
|  6 |    6 |    888 |
|  6 |    6 |    973 |
+----+------+--------+

You see there are NULLs of id 1, 3, 5 because there are no values that are NOT in your specified list. Those are the id's that you want to return.

mysql> select t1.id from t1 left join mydata on t1.id=mydata.t1id 
    and mydata.member not in (2,9,569,763) 
  where mydata.member is null;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
+----+

score 0 · Answer 3 · answered Feb 18 '19 at 20:32

0

Try to use the REGEXP function:

SELECT t1.id from t1
WHERE t1.data REGEXP '(2,9,569,763)';

answered Feb 18 '19 at 20:32

AbsoluteƵERØ

7,816
2
24
35

How to select only a specific set that includes some or all of another set in mySQL

3 Answers3