CODEWITHSUNDEEP
sqlsql-server-2008if-statement
Alias
Alias

Reputation: 413

SQL - IF statement around AND

I'm trying to create a query that will only add AND clauses where certain conditions are met.

This is what I'm after:

SELECT DISTINCT
  id
  name
  active
FROM team
WHERE id = 1234
IF team.active = 'y'
  AND team.id IN
    {
       query
    }
ELSEIF team.active = 'n'
  AND team.id IN
    {
      query
    }

But I'm not sure how to do this in SQL. Any suggestions?

Upvotes: 4

Views: 151

Answers (3)

Ajith Sasidharan
Ajith Sasidharan

Reputation: 1155

You can use decode statement for achieving the result

SELECT DISTINCT
  id
  name
  active
FROM team
WHERE id = 1234 and
team.id in (
decode (team.active,'y',(query),'n',(query))

you can also use case statment

Upvotes: -1

Ash Burlaczenko
Ash Burlaczenko

Reputation: 25475

You can use boolean logic like so

SELECT ...
FROM team
WHERE id = 1234
    AND (
        (team.active = 'y' AND team.id IN query)
        OR
        (team.active = 'n' AND team.id IN query)
    )

Did I miss the difference in the two branches?

Upvotes: 3

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103145

 SELECT DISTINCT
    id
    name
    active
    FROM team
 WHERE id = 1234
   AND (team.active = 'y'
     AND team.id IN
        {
          query
        }) 
   OR
    (team.active = 'n'
     AND team.id IN
      {
        query
      })

You use the AND and OR connectives to create the logic.

Upvotes: 5

Related Questions