SQL - Two different WHERE conditions for the same SELECT

Asked

Viewed 1,436 times

3

I am developing an SQL query which has two different WHERE functions according to the return of a variable. I tried some alternatives but none seem to fit, I need something with this logic:

IF (@show = 1) 
    WHERE d.statusId = ds.id;
ELSE
    WHERE d.statusId != 3;

Is there another alternative or something that meets my need?

sql query

  • What the SGBD used?

3 answers

3


You will have to evaluate the variable @show within itself WHERE:

SELECT      *
FROM        xxx d
INNER JOIN  yyy ds ON ds.id_xxx = d.id
WHERE       d.statusId =  (CASE WHEN @show = 1  THEN ds.id  END)
        OR  d.statusId <> (CASE WHEN @show <> 1 THEN 3      END)

Not knowing the structure of the tables and the information that each one contains, is a "shot in the dark", but in principle works.

  • It worked! Thank you very much :D

3

Use the OR:

WHERE (@show = 1 AND d.statusId = ds.id)
   OR d.statusId != 3;

2

From what I understand it should solve your problem:

set @show = 1;

SELECT * FROM banco.tabela
where if(@show = 1,d.statusId = ds.id,d.statusId != 3);

"This is a functional example ai Voce adapts your needs"

This is the mysql documentation link if you want to give a study. https://dev.mysql.com/doc/refman/8.0/en/if.html

  • Just note that the if does not work at all SGBDs

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.