3

I am using the JPA with JPQL queries to retrieve data from a MySQL DB. But my problem is rather logical than language specific (I think)...

I would like to SELECT all entries of a table WHERE one column is in BETWEEN a list of ranges.

For example:

SELECT x FROM myTable 
WHERE x BETWEEN 2 AND 5 
   OR x BETWEEN 7 AND 9

While the ranges (2-5, 7-9) come from another table:

 RANGES         ex:   id | from | to
––––––––––            –––––––––––––––
 id:   INT              1|     2|   5
 from: INT              2|     7|   9
 to:   INT              3|     9|  15
                        4|    17| null

My problem a)
is, that the Ranges list is dynamic and could be either only one range, like
x BETWEEN 2 AND 5
or multiple ranges like
x BETWEEN 2 AND 5 OR x BETWEEN 7 AND 9 OR x BETWEEN ....

My problem b)
is, that the Ranges could also be open like
x >= 17

My problem c)
is, that there are other WHERE conditions, limiting the results. Some of those WHERE conditions are more "limiting" than the BETWEEN conditions. So they come first, to make the whole query faster. Let's say for example
... WHERE x%2=0 AND x BETWEEN 2 AND 5

For c) I thought about sub-queries, for example:

SELECT x FROM myTable 
WHERE x IN (
        SELECT x FROM myTable 
        WHERE x BETWEEN 2 AND 5 
          OR  x BETWEEN 7 AND 9
     )
 AND y .. something something

The "good" thing is, I use Java and the JPA to create my queries, so I could create the queries dynamically. But before abusing String concatenation to dynamically create SQL queries, I was hoping to find a cleaner solution in pure SQL (or even better in JPQL)

GameDroids
  • 5,584
  • 6
  • 40
  • 59

2 Answers2

4

First and is the wrong logic. "x" cannot be both between 2 and 5 and 7 and 9. It is a mathematical impossibility. So, I assume you intend or.

Then, you can do this with exists:

select t1.*
from table1 t1
where exists (select 1
              from table2 t2
              where t1.x between t2.fromval and t2.toval
             );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I can't believe it so "simple" :) Thank you so much!! Out of curiosity: what does the `1` in `select 1 from..` do exactly? – GameDroids Feb 12 '16 at 13:23
  • 1
    @GameDroids you have to select one column when using exists. Selecting 1 is the easiest/fastest way of doing it. But you could select "Hey I am really hungry" and it would still work. – Stan Shaw Feb 12 '16 at 13:28
  • 1
    @StanShaw Interesting! Thanks for explaining. I guess I'll stick to `1` then :) – GameDroids Feb 12 '16 at 13:31
2

Answering the problem (b):

You should change WHERE condition in @Gordon-Linoff answer if you have conditions with open bounds:

 where (t1.x between t2.fromval and t2.toval)
       OR ((t1.x >= t2.fromval)  AND (t2.toval IS NULL))
       OR ((t1.x <= t2.toval)  AND (t2.fromval IS NULL))
       OR ((t2.toval IS NULL) AND (t2.fromval IS NULL)) --super conditions always TRUE :)

Also you can use this condition:

where t1.x between COALESCE(t2.fromval,t1.x) and COALESCE(t2.toval,t1.x)
valex
  • 23,966
  • 7
  • 43
  • 60
  • thanks ! I just started to work on problem b :) For now I think I'll go with `where t1.x between t2.fromval and COALESCE(t2.toval,t1.x)` . Didn't know `COALESCE` before - such a nice command – GameDroids Feb 12 '16 at 14:09
  • Upvoting for Coalesce – Stan Shaw Feb 12 '16 at 14:21