Checking if any of a list of values falls within a table of ranges
- by Conspicuous Compiler
I'm looking to check whether any of a list of integers fall in a list of ranges. The ranges are defined in a table defined something like:
# Extra Type Field Default Null Key
0 int(11) rangeid 0 NO PRI
1 int(11) max 0 NO MUL
2 int(11) min 0 NO MUL
Using MySQL 5.1 and Perl 5.10.
I can check whether a single value, say 7, is in any of the ranges with a statement like
SELECT 1
FROM range
WHERE 7 BETWEEN min AND max
If 7 is in any of those ranges, I get a single row back. If it isn't, no rows are returned.
Now I have a list of, say, 50 of these values, not stored in a table at present. I assemble them using map:
my $value_list = '('
. ( join ', ', map { int $_ } @values )
. ')'
;
I want to see if any of the items in the list fall inside of any of the ranges, but am not particularly concerned with which number nor which range. I'd like to use a syntax such as:
SELECT 1
FROM range
WHERE (1, 2, 3, 4, 5, 6, 7, 42, 309, 10000) BETWEEN min AND max
MySQL kindly chastises me for such syntax:
Operand should contain 1 column(s)
I pinged #mysql who were quite helpful. However, having already written this up by the time they responded and thinking it'd be helpful to fix the answer in a more permanent medium, I figured I'd post the question anyhow. Maybe SO will provide a different solution?
