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

Mysql – `CASE values1 WHEN values2` compare multiple columns, instead of deeply nested `if(values1 = values2)`

I want to compare system table points against a user’s input answer, and give every specific combination a score, and order by that. The 2 columns to compare into a score are po.points and an.answer. Both columns can have a value from 1 – 3, so that’s 9 different combinations. It will be 0 – 3 soon, so 16 different combinations. Currently, I do this, and that works:

SELECT if(po.points = 3 and an.answer = 3, 8, if(po.points = 2 and an.answer = 2, 5, if(po.points = 1 and an.answer = 1, 3, if(po.points = 3 and an.answer = 2, -3, if(po.points = 3 and an.answer = 1, -10, if(po.points = 2 and an.answer = 3, 0, if(po.points = 2 and an.answer = 1, -2, if(po.points = 1 and an.answer = 3, -1, 0)))))))) as score

What a mess! But that’s not so important, because that blob of numbers and ifs is compiled from a configured matrix somewhere else. It doesn’t have to be pretty. But it’s also LONG, and that’s just 9 combinations. With 16 combinations (soon), it’s even way worse, and even more if() that MySQL has to parse.

So I was thinking CASE to the rescue! Look at this readable beauty!:

SELECT case (po.points, an.answer) when (3, 3) then 8 when (2, 2) then 5 when (1, 1) then 3 when (3, 2) then -3 when (3, 1) then -10 when (2, 3) then 0 when (2, 1) then -2 when (1, 3) then -1 else 0 end as score

It’s shorter, it’s more readable, it’s 1 comparing structure instead of separate ifs, it’s better in every way, MySQL must love this! But no, MySQL does not love OR accept this:

Cardinality violation: 1241 Operand should contain 1 column(s)

Alright, fine, maybe I can force them into being 1 column. CONCAT() to the rescue! Already it didn’t feel great anymore, but let’s give it a shot anyway:

SELECT case concat(po.points, '-', an.answer) when concat(3, '-', 3) then 8 when concat(2, '-', 2) then 5 when concat(1, '-', 1) then 3 when concat(3, '-', 2) then -3 when concat(3, '-', 1) then -10 when concat(2, '-', 3) then 0 when concat(2, '-', 1) then -2 when concat(1, '-', 3) then -1 else 0 end as score

Not nearly as pretty, not nearly as shot, and now it’s just concatting all the numbers in the database. That’s gross, and it can’t be fast. But it works.

(Actually, it’s about the same speed as the 9-if-inception, surprisingly, but I stil hate it.)

Is there a faster way to do the 9-combinations-comparison (will be 16-combination-comparison) that’s faster than 9 ifs? There might not be, that’s fine too. The results are acceptably fast, and I can’t imagine the ifs are the slowdown, but I’m still curious how to do this better.

Tags:

2

Answers


  1. 0

    This can be done using CASE clause as follows :

      case
    when po.points = 3 and an.answer = 3  then 8 
    when po.points = 2 and an.answer = 2  then 5
    when po.points = 1 and an.answer = 1  then 3 
    when po.points = 3 and an.answer = 2  then -3 
    when po.points = 3 and an.answer = 1  then -10 
    when po.points = 2 and an.answer = 3  then 0 
    when po.points = 2 and an.answer = 1  then -2 
    when po.points = 1 and an.answer = 3  then -1 
    else 0 
  end as score;

    Demo here

    Login or Signup to reply.
  2. 0 
    SELECT COALESCE(map.score, 0) AS score
FROM po
JOIN an ON ...whatever your join condition is...
LEFT OUTER JOIN (
  VALUES ROW(3,3,8), ROW(2,2,5), ROW(1,1,3), ROW(3,2,-3), ROW(3,1,-10), ROW(2,3,0), ROW(2,1,-2), ROW(1,3,-1)
) AS map(points, answer, score) ON po.points = map.points AND an.answer = map.answer;

    Demo: https://dbfiddle.uk/sRvBdqkH

    Or if the mapping values are consistent, you could store your map as a real table and join to it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search