SQL Reference values in another table without a join

I am having an issue referencing another table that cannot be joined. Table 1 has a name and value that I want to look up in table 2 and use a between case function to get the category.

In this example, I want to return:

Smith, 99, GOOD | Hill, 96, BAD

TABLE 1

| Name  | Number 
----------------
| Smith | 99       
| Hill  | 96    
----------------

TABLE 2

--------------------------
| Category | Min |  Max |  
--------------------------
| GOOD     | 98  | 100  |  
| BAD      | 95  | 97   |  
--------------------------

>Solution :

You certainly can use a JOIN. You can use BETWEEN in the ON condition.

SELECT t1.name, t1.number, t2.category
ON Table1 AS t1
JOIN Table2 AS t2 ON t1.number BETWEEN t2.min AND t2.max

If you thought that joins could only use foreign key equality, you’re mistaken.