CODEWITHSUNDEEP
mysql
DHHJ
DHHJ

Reputation: 143

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

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);

Upvotes: 1

Views: 157

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562991

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 |
+----+

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31832

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

Upvotes: 1

AbsoluteƵERØ
AbsoluteƵERØ

Reputation: 7880

Try to use the REGEXP function:

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

Upvotes: 0

Related Questions