How to filter a set of numbers between two columns?

Question

I have a table with min and max columns. I want to look for strings where a set of numbers is between this range of min and max .

Example: I have a set (3, 4, 11, 18) .

• My query filtering might look:

   (3 between min and max) OR (4 between min and max) OR (11 between min and max) OR (18 between min and max) 

• But I wanted to know if there is something like better:

   (3,4,11,12) between min and max 

  what will be like # 1

I need this because the set can vary between different requests. Any help or suggestions are welcome.

A table in a simpler context is a class (classid, classname, minage, maxage). There are thousands of classes - so I get a web search request for classes with a certain age, for example (3,4,11,12), where the user is looking for classes aged 3, 4, 11 and 12.

Currently my query looks like this: select * from the class where ((3 between min and max) OR (4 between min and max) OR (11 between min and max) OR (18 between min and max))

Answer 1

Sort of

 SELECT * FROM MyTable AS T WHERE EXISTS ( SELECT * FROM MySet AS S WHERE S.val BETWEEN T.my_min AND T.my_max ); 

Answer 2

We could help you more if you could add more information by posting your table layout. However, this sample query may give you some hint.

Request example:

SELECT SampleValue, ColB, ColC FROM TableName WHERE SampleValue in (1,2,3)

Answer 3

Another way would be to create a table (once) and fill it with all possible ages (from 1 to 101):

 CREATE TABLE Age ( allowed TINYINT , PRIMARY KEY (allowed) ) ; INSERT INTO Age VALUES (1), (2), (3), ... (101) ; 

Use this in your search query:

 SELECT * FROM Class WHERE EXISTS ( SELECT * FROM Age WHERE Age.allowed BETWEEN Class.minage AND Class.maxage AND Age.allowed IN (3, 4, 11, 18) )