CODEWITHSUNDEEP
sqlfirebird
Aleksa Ristic
Aleksa Ristic

Reputation: 2499

Building complex SQL command

I am using sql for select, update, insert and some other features, but only simple ones until now. Now I need to write a complex command.

I have looked at using case but I do not know how to implement it.

This is what it should look like:

SELECT KVIZ_ISTORIJA.ID AS ISTORIJAID, KVIZ_PITANJA.PITANJE1, ~TACNO~
FROM KVIZ_ISTORIJA 
INNER JOIN KVIZ_PITANJA ON KVIZ_ISTORIJA.PITANJEID = KVIZ_PITANJA.PITANJEID 
WHERE REZULTATID = 12

I used ~TACNO~ to point out where I need a conditional value.

How I would write the condition in C# is like this (I will use column names from table as variables):

int ~TACNO~ = -1;
int I = -1;

if(KVIZ_PITANJA.ODGOVOR1_TACAN == 1)
    I = 1;
else if(KVIZ_PITANJA.ODGOVOR2_TACAN == 1)
    I = 2;
else if(KVIZ_PITANJA.ODGOVOR3_TACAN == 1)
    I = 3;
else if(KVIZ_PITANJA.ODGOVOR4_TACAN == 1)
    I = 4;
else if(KVIZ_PITANJA.ODGOVOR5_TACAN == 1)
    I = 5;

switch(I)
{
    case 1:
        if(KVIZ_ISTORIJA.ODGOVORENO1 = 1)
            ~TACNO~ = 1;
       break;
    case 2:
        if(KVIZ_ISTORIJA.ODGOVORENO2 = 1)
            ~TACNO~ = 1;
       break;
    case 3:
        if(KVIZ_ISTORIJA.ODGOVORENO3 = 1)
            ~TACNO~ = 1;
       break;
    case 4:
        if(KVIZ_ISTORIJA.ODGOVORENO4 = 1)
            ~TACNO~ = 1;
       break;
    case 5:
        if(KVIZ_ISTORIJA.ODGOVORENO5 = 1)
            ~TACNO~ = 1;
       break;
}

How can I write the equivalent of this C# condition in SQL query?

Upvotes: 1

Views: 68

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108993

You can use a searched CASE like this:

case 
  when (KVIZ_PITANJA.ODGOVOR1_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO1 = 1) then 1
  when (KVIZ_PITANJA.ODGOVOR2_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO2 = 1) then 1
  when (KVIZ_PITANJA.ODGOVOR3_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO3 = 1) then 1
  when (KVIZ_PITANJA.ODGOVOR4_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO4 = 1) then 1
  when (KVIZ_PITANJA.ODGOVOR5_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO5 = 1) then 1
  else -1
end

You could also do it as a single when clause, by joining the conditions using OR, but I think that is less readable:

case 
  when (KVIZ_PITANJA.ODGOVOR1_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO1 = 1)
    or (KVIZ_PITANJA.ODGOVOR2_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO2 = 1)
    or (KVIZ_PITANJA.ODGOVOR3_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO3 = 1)
    or (KVIZ_PITANJA.ODGOVOR4_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO4 = 1)
    or (KVIZ_PITANJA.ODGOVOR5_TACAN = 1 and KVIZ_ISTORIJA.ODGOVORENO5 = 1) then 1
  else -1
end

But as suggested by Arioch'The in the comments: consider normalizing your design, so you don't have repeating columns in a single table, but instead multiple rows with a discriminator column. It would simplify things like this.

Upvotes: 1

Related Questions