Christos Lytras Christos Lytras - 2 months ago 17
SQL Question

MySQL: Match a set of values to column values with a different order

I have a table with a date and some fields with integer numbers like this:

SELECT * FROM numbers LIMIT 10;

+----+------------+----+----+----+----+----+----+----+
| id | gen_date | n1 | n2 | n3 | n4 | n5 | n6 | n7 |
+----+------------+----+----+----+----+----+----+----+
| 1 | 1990-12-27 | 22 | 43 | 18 | 23 | 16 | 7 | 0 |
| 2 | 1990-12-19 | 39 | 11 | 38 | 23 | 24 | 36 | 0 |
| 3 | 1990-12-12 | 44 | 12 | 10 | 48 | 14 | 4 | 0 |
| 4 | 1990-12-05 | 2 | 4 | 5 | 46 | 48 | 49 | 0 |
| 5 | 1991-12-25 | 10 | 2 | 44 | 49 | 29 | 34 | 0 |
| 6 | 1991-12-18 | 6 | 19 | 42 | 4 | 12 | 28 | 0 |
| 7 | 1991-12-11 | 1 | 12 | 42 | 7 | 31 | 25 | 0 |
| 8 | 1991-12-04 | 18 | 47 | 31 | 30 | 10 | 35 | 0 |
| 9 | 1991-11-27 | 38 | 33 | 39 | 47 | 13 | 11 | 0 |
| 10 | 1991-11-20 | 23 | 7 | 22 | 18 | 43 | 16 | 0 |
+----+------------+----+----+----+----+----+----+----+


All row numbers are unique
nx <> ny
.

I want to match a set of 6 numbers against columns
n1 to n6
regardless or the value order.

For example, at row with
ID=1
there is
22, 43, 18, 23, 16, 7
and the row with
ID=10
has the same numbers
23, 7, 22, 18, 43, 16
. I have written a query to much every row containing all the numbers in a column set of each row. If I need to search for numbers
7, 16, 18, 22, 23, 43
then the result returns both rows
ID=1
and
ID=10
.

I got it working by using the
FIELD
function to search each number through all field values like this:

SELECT * FROM numbers
WHERE
FIELD(7, n1, n2, n3, n4, n5, n6) > 0 AND
FIELD(16, n1, n2, n3, n4, n5, n6) > 0 AND
FIELD(18, n1, n2, n3, n4, n5, n6) > 0 AND
FIELD(22, n1, n2, n3, n4, n5, n6) > 0 AND
FIELD(23, n1, n2, n3, n4, n5, n6) > 0 AND
FIELD(43, n1, n2, n3, n4, n5, n6) > 0;

+----+------------+----+----+----+----+----+----+----+
| id | gen_date | n1 | n2 | n3 | n4 | n5 | n6 | n7 |
+----+------------+----+----+----+----+----+----+----+
| 1 | 1990-12-27 | 22 | 43 | 18 | 23 | 16 | 7 | 0 |
| 10 | 1991-11-20 | 23 | 7 | 22 | 18 | 43 | 16 | 0 |
+----+------------+----+----+----+----+----+----+----+


This works, but I have some concerns about the way it works and if that approach is performance wise when using against tenths of thousands of rows.

Is there another better (or maybe more simple) way to accomplish something like this?

Answer Source

Your query can be simplified to:

SELECT * 
FROM numbers
WHERE
 7 IN (n1, n2, n3, n4, n5, n6) AND
 16 IN (n1, n2, n3, n4, n5, n6) AND
 18 IN (n1, n2, n3, n4, n5, n6) AND 
 22 IN (n1, n2, n3, n4, n5, n6) AND 
 23 IN (n1, n2, n3, n4, n5, n6) AND 
 43 IN (n1, n2, n3, n4, n5, n6) ;