Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Joining two tables with a conditon that is dictated by the columns of one of them

I have two tables where the credit_limit of the customer table should determine the rating of each customer.
In the end, the output should look like this, where the credit_limit determines the rating for each customer:

cust_id credit_limit rating
400300 6000 Excellent
400801 750 Standard
401250 750 Standard

I was initially thinking about using JOIN to combine these two tables, but then realized that it’s probably better to just list out the table as alias.

However, I’m not so sure about the syntax or approaches to setting up this condition logic where "c.credit >0 AND c.credit <=1000" SET "Excellent."

customer tables
rating table

Below is what I have so far, but I don’t think IF clause will do much here. How do I get the output above?

SELECT c.cust_id, 
        c.credit_limit, 
        cred.rating
FROM customers AS c, credit_ratings AS cred
WHERE IF(c.credit_limit);
Tags:

2

Answers


  1. 0

    Maybe something like this:

    CREATE TABLE customer_ratings AS
SELECT
    c.id AS customer_id,
    c.credit_limit,
    r.rating
FROM
    customer c
JOIN
    ratings_ranges r
ON
    c.credit_limit BETWEEN r.low_limit AND r.high_limit;
    Login or Signup to reply.
  2. 0

    If you create another table with the results, it could make it more difficult to keep the data in sync. You could just join the tables as normal. Your query is almost there. Assuming the ranges in the cred table are contiguous and non-overlapping as you have shown, I would do this…

    SELECT c.cust_id, 
       c.credit_limit, 
       cred.rating
FROM customers AS c, 
     credit_ratings AS cred
WHERE c.credit_limit between cred.low_limit and cred.high_limit;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search