Joining onto a table that doesn't have ranges, but requires ranges

Trying to find the best way to write this SQL statement.

I have a customer table that has the internal credit score of that customer. Then i have another table with definitions of that credit score. I would like to join these tables together, but the second table doesn't have any way to link it easily.

The score of the customer is an integer between 1-999, and the definition table has these columns:

Score
Description

And these rows:

60   LOW
99   MED
999  HIGH

So basically if a customer has a score between 1 and 60 they are low, 61-99 they are med, and 100-999 they are high.

I can't really INNER JOIN these, because it would only join them IF the score was 60, 99, or 999, and that would exclude anyone else with those scores.

I don't want to do a case statement with the static numbers, because our scores may change in the future and I don't want to have to update my initial query when/if they do. I also cannot create any tables or functions to do this- I need to create a SQL statement to do it for me.

EDIT:

A coworker said this would work, but its a little crazy. I'm thinking there has to be a better way:

SELECT 
  internal_credit_score
  (
    SELECT
      credit_score_short_desc
    FROM
      cf_internal_credit_score
    WHERE
      internal_credit_score = (
                                SELECT 
                                  max(credit.internal_credit_score) 
                                FROM 
                                  cf_internal_credit_score credit  
                                WHERE 
                                  cs.internal_credit_score <= credit.internal_credit_score
                                  AND credit.internal_credit_score <= (
                                                                        SELECT
                                                                          min(credit2.internal_credit_score)
                                                                        FROM
                                                                          cf_internal_credit_score credit2
                                                                        WHERE
                                                                          cs.internal_credit_score <= credit2.internal_credit_score
                                   开发者_开发技巧                                   )
                              )
  )
FROM 
  customer_statements cs

---

try this, change your table to contain the range of the scores:

ScoreTable
-------------
LowScore int
HighScore int
ScoreDescription string

data values

LowScore HighScore ScoreDescription 
-------- --------- ----------------
1        60        Low
61       99        Med
100      999       High

query:

Select
    .... , Score.ScoreDescription 
    FROM YourTable
    INNER JOIN Score ON YourTable.Score>=Score.LowScore 
        AND YourTable.Score<=Score.HighScore
    WHERE ...

---

Assuming you table is named CreditTable, this is what you want:

select * from
(
    select Description, Score
    from CreditTable
    where Score > 80 /*client's credit*/
    order by Score
)
where rownum = 1

Also, make sure your high score reference value is 1000, even though client's highest score possible is 999.

Update

The above SQL gives you the credit record for a given value. If you want to join with, say, Clients table, you'd do something like this:

select 
  c.Name,
  c.Score,
  (select Description from 
      (select Description from CreditTable where Score > c.Score order by Score)
   where rownum = 1)
from clients c

I know this is a sub-select that executed for each returning row, but then again, CreditTable is ridiculously small and there will be no significant performance loss because of the the sub-select usage.

---

You can use analytic functions to convert the data in your score description table to ranges (I assume that you meant that 100-999 should map to 'HIGH', not 99-999).

SQL> ed
Wrote file afiedt.buf

  1  with x as (
  2    select 60 score, 'Low' description from dual union all
  3    select 99, 'Med' from dual union all
  4    select 999, 'High' from dual
  5  )
  6  select description,
  7         nvl(lag(score) over (order by score),0) + 1 low_range,
  8         score high_range
  9*   from x
SQL> /

DESC  LOW_RANGE HIGH_RANGE
---- ---------- ----------
Low           1         60
Med          61         99
High        100        999

You can then join this to your CUSTOMER table with something like

SELECT c.*, 
       sd.*
  FROM customer c,
       (select description,
               nvl(lag(score) over (order by score),0) + 1 low_range,
               score high_range
          from score_description) sd
 WHERE c.credit_score BETWEEN sd.low_range AND sd.high_range