Alexis RS Alexis RS - 15 days ago 5
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

Yes. Just use a join:

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