sql - MySQL/MariaDB find one or more numbers in list, matching lottery numbers with past results

Question

I have a MariaDB table with an archive of past lottery results, imagine EuroMillions or Powerball lotteries.

For example on EuroMillions numbers go from 1 to 50 and then the extra balls from 1 to 12, each result is 5 numbers form the main pool and 2 from the extra pool. So my historic results table could look like this:

Lottery Results table

(other columns like id, date, draw number, etc) | main_numbers | extra_numbers | (timestamp columns)
... | 1,2,3,4,5 | 1,2 | ...
... | 3,12,34,35,45 | 5,11 | ...
... | 4,15,34,39,45 | 10,11 | ...
... | 7,11,25,28,44 | 10,12 | ...
(you get the idea, I have thousands of records...)

So I could select main_numbers and get result "3,12,34,35,45" for that second example row. And for the extra_numbers I would get "5,11".

What I want is to given a set of numbers for main and extra to see if they match any of my results, finding any number of numbers (numbered lottery balls).

So for example if I SELECT to find main_numbers "5,9,22,34,45" with extra_numbers "2,11" I would get (from my extracted example) two records:

... | 3,12,34,35,45 | 5,11 | ...
... | 4,15,34,39,45 | 10,11 | ...

Matching two main numbers and one extra number, in this case finding lottery prizes in the results table. Makes sense?

I'm using MariaDB and I'm a bit lost on how to proceed, I tried WHERE IN, FIELD_IN_SET, etc. Is there a way to perform a SELECT to find results in only one statement or do I have to pick all records and then iterate elsewhere, php for example?

My aim would be to have it in one statement, so I could just send the numbers and get the matching records... Possible?

I hope this makes sense. Many thanks for your answers.

question from:https://stackoverflow.com/questions/65882597/mysql-mariadb-find-one-or-more-numbers-in-list-matching-lottery-numbers-with-pa

Answer 1

Consider the following.

For simplicity, let's say that a lottery comprises 3 main balls, and two bonus balls:

DROP TABLE IF EXISTS lottery_results;

CREATE TABLE lottery_results
(draw_id INT NOT NULL
,ball_no INT NOT NULL
,ball_val INT NOT NULL
,PRIMARY KEY(draw_id,ball_no)
);

INSERT INTO lottery_results VALUES
(1,1,22),
(1,2,35),
(1,3,62),
(1,4,27),
(1,5,17),
(2,1,18),
(2,2,33),
(2,3,49),
(2,4, 4),
(2,5,35);

And we want to find all results where 34, 35, or 36 were drawn as a main number...

SELECT draw_id
  FROM lottery_results 
 WHERE ball_no <=3 
   AND ball_val IN(34,35,36);

+---------+
| draw_id |
+---------+
|       1 |
+---------+