sql - performing join when matching value is in between two column values

Question

I have a table1 like below, table1 is very huge

Col1 Col2

A     1

B     5

A     7

My table2 is as below. My real table2 is very big and I cannot explode it due to size restrictions.

Col3 Col4  Col5

0     2      x

4     5.5    y

6     7.5    z

In such case is there a way to join table1 and table2 such that

where table1.col2 is between table2.col3 and table2.col4

The output that I want is below

Col1 Col2 Col5

A     1    x

B     5    y

A     7    z

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You need INNER JOIN with BETWEEN as join condition

SELECT t1.col1, 
       t1.col2, 
       t2.col5 
FROM   table1 t1 
       JOIN table2 t2 
         ON t1.col2 BETWEEN t2.col3 AND t2.col4

Note : you have mentioned where table1>>col2 is between table2>>col4 and table2>>col5 but it should be where table1>>col2 is between table2>>col3 and table2>>col4

sql - performing join when matching value is in between two column values

Sign up