Building complex SQL command

Question

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?

Use "Searched case" variant as described at firebirdsql.org/file/documentation/reference_manuals/… — Arioch 'The
– Arioch 'The, Commented Jul 12, 2018 at 8:43
I can use it instead of switch but what about first part (storing I) — Aleksa Ristic
– Aleksa Ristic, Commented Jul 12, 2018 at 8:45
You do not need intermediate I at all. You can set value for TACNO in the if-ladder directly. — Arioch 'The
– Arioch 'The, Commented Jul 12, 2018 at 8:46
First of all, you made a typical novice mistake was putting same values into different columns. You have to remake the table, so there would be one KVIZ_ISTORIJA.ODGOVORENO column, and 5 rows with some ID =1...5. That is anti-SQL, anti-relational. Now you struggle with consequences and there would be more and more. Same way there should be one column KVIZ_PITANJA.ODGOVOR_TACAN and 5 rows with ID = 1 to 5. Restructure the database before it is too late.... — Arioch 'The
– Arioch 'The, Commented Jul 12, 2018 at 8:54

Mark Rotteveel · Accepted Answer · 2020-12-31 19:05:23Z

1

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.

edited Dec 31, 2020 at 19:05

answered Jul 12, 2018 at 15:31

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aleksa Ristic Over a year ago

I have already done it like you showed in first example but still thanks for posting this for future troubleshooters.

Collectives™ on Stack Overflow

Building complex SQL command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related