Alexis RS Alexis RS - 10 months ago 51
SQL Question

SQL get records from table where results from another table is in the range defined by Col1 and Col2?

I have the following ranges from a query:

Col1 Col2
--------------
100-200
200-300
300-400


and this vector from another query:

Nbr
----
119
351
149


I want to get the ranges for the numbers on the vector.

Is there a way to do this in SQL without recurring to iterations? Something like:

SELECT Col1, Col2
FROM TB1
WHERE (SELECT Nbr FROM TB2) BETWEEN Col1 and Col2


The above query doesn't work because multiple results are returned.

Thank you.

Answer Source

Yes. Just use a join:

SELECT TB1.Col1, TB1.Col2
FROM TB1 JOIN
     TB2
     ON TB2.Nbr BETWEEN TB1.Col1 and TB1.Col2;