Help me rewrite this query

Question

I have this query:

SELECT diamond_id, carat, clarity, color, cut,
    CASE clarity
        WHEN "FL" THEN 0
        WHEN "IF" THEN 1
        WHEN "VVS1" THEN 2
        WHEN "VVS2" THEN 3
        WHEN "VS1" THEN 4
        WHEN "VS2" THEN 5
        WHEN "SI1" THEN 6
        WHEN "SI2" THEN 7
        WHEN "I1" THEN 8
        WHEN "I2" THEN 9
        WHEN "I3" THEN 10
        ELSE -1
    END AS clarity_score,
    CASE cut
        WHEN "ideal" THEN 0
        WHEN "excellent" THEN 1
        WHEN "very good" THEN 2
        WHEN "good" THEN 3
        WHEN "fair" THEN 4
        WHEN "poor" THEN 5
        ELSE -1
    END AS cut_score    
    FROM rapnet_diamonds
 WHERE 
    shape IN ("round","princess","oval") 
 AND (carat BETWEEN ".1" AND "5") 
 AND (color BETWEEN "D" AND "Z") 
 AND (clarity_score BETWEEN "0" AND "10") 
 AND (cut_score BETWEEN "0" AND "5")

As you can see I generate the clarity_score and cut_score columns on the fly so I can use them in my WHERE clause.

First of all, this is giving me an error telling me that clarity_score doesn't exist. SO I thought maybe this is one of those cases where I need to use HAVING instead?

so I changed my where clause to:

WHERE 
    shape IN ("round","princess","oval") 
 AND (carat BETWEEN ".1" AND "5") 
 AND (color BETWEEN "D" AND "Z") 
HAVING 
    (clarity_score BETWEEN "0" AND "10") 
 AND (cut_score BETWEEN "0" AND "5")

I got no error, but 0 results... This should be returning over 100K. So I wrote this wrong somewhere?

BEFORE YOU START rewriting this however, I DO NOT want to SELECT those 2 generated fields, is there a way I can write it so I am not retrieving those 2 fields, just using them in the where clause?

Answer 1

Doesn't this work? (I have limited experience with MySQL.)

SELECT diamond_id, carat, clarity, color, cut
  FROM rapnet_diamonds
 WHERE 
    shape IN ("round","princess","oval") 
 AND (carat BETWEEN ".1" AND "5") 
 AND (color BETWEEN "D" AND "Z") 
 AND (  CASE clarity
        WHEN "FL" THEN 0
        WHEN "IF" THEN 1
        WHEN "VVS1" THEN 2
        WHEN "VVS2" THEN 3
        WHEN "VS1" THEN 4
        WHEN "VS2" THEN 5
        WHEN "SI1" THEN 6
        WHEN "SI2" THEN 7
        WHEN "I1" THEN 8
        WHEN "I2" THEN 9
        WHEN "I3" THEN 10
        ELSE -1
    END BETWEEN "0" AND "10") 
 AND (  CASE cut
        WHEN "ideal" THEN 0
        WHEN "excellent" THEN 1
        WHEN "very good" THEN 2
        WHEN "good" THEN 3
        WHEN "fair" THEN 4
        WHEN "poor" THEN 5
        ELSE -1
    END BETWEEN "0" AND "5")

Answer 2

I would put the case statement logic into a look up decoder table, and join to that. Then you can use the columns in the where clause, and it simplifies the SQL.

something like

create table ClarityCode
(
   Clarity char(x) not null
,  CodeID int not null
)

Answer 3

why not use IN?

SELECT diamond_id, carat, clarity, color, cut
FROM rapnet_diamonds
WHERE
shape IN ("round","princess","oval") AND
(carat BETWEEN ".1" AND "5") AND
(color BETWEEN "D" AND "Z") AND
clarity IN ('FL', 'IF', 'VVS1', 'VVS2', 'VS1', 'VS2', 'SI1', 'SI2', 'I1', 'I2', 'I3') AND
cut IN ('ideal', 'excellent', 'very good', 'good', 'fair', 'poor')

Answer 4

I would take your query and modify you code little bit to get it work

SELECT * FROM 
  (
    SELECT diamond_id, carat, clarity, color, cut, 
        CASE clarity 
            WHEN "FL" THEN 0 
            WHEN "IF" THEN 1 
            WHEN "VVS1" THEN 2 
            WHEN "VVS2" THEN 3 
            WHEN "VS1" THEN 4 
            WHEN "VS2" THEN 5 
            WHEN "SI1" THEN 6 
            WHEN "SI2" THEN 7 
            WHEN "I1" THEN 8 
            WHEN "I2" THEN 9 
            WHEN "I3" THEN 10 
            ELSE -1 
        END AS clarity_score, 
        CASE cut 
            WHEN "ideal" THEN 0 
            WHEN "excellent" THEN 1 
            WHEN "very good" THEN 2 
            WHEN "good" THEN 3 
            WHEN "fair" THEN 4 
            WHEN "poor" THEN 5 
            ELSE -1 
        END AS cut_score     
        FROM rapnet_diamonds 
     WHERE  
        shape IN ("round","princess","oval")  
     AND (carat BETWEEN ".1" AND "5")  
     AND (color BETWEEN "D" AND "Z")
)t 
WHERE   
     (clarity_score BETWEEN "0" AND "10")  
     AND (cut_score BETWEEN "0" AND "5") 

You cannot use Case variables in where clause

Answer 5

In response to the desire for more explanation for the split-table idea:

Rather than having everything in a CASE statement, you store the clarity information in a second table. It might look something like this:

Clarity_Table
-----------------------
Clarity_score | clarity
-----------------------
0             | FL
1             | IF
2             | VVS1
3             | VVS2
4             | VS1
5             | VS2
...
10            | I3

And the same thing for Cut_Table, hopefully you get the idea from above.

then, change your query to:

SELECT diamond_id, carat, clarity, color, cut, clarity_score, cut_score    
    FROM rapnet_diamonds JOIN clarity_table USING(clarity)
                         JOIN cut_table USING(cut)
 WHERE 
    shape IN ("round","princess","oval") 
 AND (carat BETWEEN ".1" AND "5") 
 AND (color BETWEEN "D" AND "Z") 
 AND (clarity_score BETWEEN "0" AND "10") 
 AND (cut_score BETWEEN "0" AND "5")

Then, rather than hard-coding the scores, it does a join between the tables. I doubt it will get updated very often, but it would make changing things later easier, and makes overall code maintenance & readability MUCH easier.

Answer 6

You can do the following.

Create two look-up tables

create table clarity_lookup 
    (clarity_score int,
    clarity char(5))

create table cut_lookup 
    (cut_score int,
    cut char(10))

Load the look-up tables with data

INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (0, 'FL')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (1, 'IF')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (2, 'VVS1')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (3, 'VVS2')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (4, 'VS1')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (5, 'VS2')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (6, 'SI1')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (7, 'SI2')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (8, 'I1')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (9, 'I2')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (10, 'I3')

INSERT INTO cut_lookup (cut_score, cut) VALUES (0, 'ideal')
INSERT INTO cut_lookup (cut_score, cut) VALUES (1, 'excellent')
INSERT INTO cut_lookup (cut_score, cut) VALUES (2, 'very good')
INSERT INTO cut_lookup (cut_score, cut) VALUES (3, 'good')
INSERT INTO cut_lookup (cut_score, cut) VALUES (4, 'fair')
INSERT INTO cut_lookup (cut_score, cut) VALUES (5, 'poor')

Your query would look like the following. In summary, you would join your new tables, which represent the data in your original CASE clauses, to the diamonds table based on the scores, but display the descriptions in your SELECT clause.

SELECT rapnet_diamonds.diamond_id, rapnet_diamonds.carat,
    clarity_lookup.clarity, rapnet_diamonds.color, cut_lookup.cut
FROM rapnet_diamonds
LEFT OUTER JOIN clarity_lookup ON rapnet_diamonds.clarity_score = clarity_lookup.clarity_score
LEFT OUTER JOIN cut_lookup ON rapnet_diamonds.cut_score = cut_lookup.cut_score
WHERE rapnet_diamonds.shape IN ('round','princess','oval') 
    AND (rapnet_diamonds.carat BETWEEN .1 AND 5) 
    AND (rapnet_diamonds.color BETWEEN 'D' AND 'Z') 
    AND (rapnet_diamonds.clarity_score BETWEEN 0 AND 10) 
    AND (rapnet_diamonds.cut_score BETWEEN 0 AND 5)