CODEWITHSUNDEEP
sqlsql-servert-sql
mshwf
mshwf

Reputation: 7449

Conditional Conditions in SQL

I want to filter a query based on a conditional condition, for example if ProjectId equals 1 then filter based on some condition otherwise, another diffrerent condition:

something that- if possible in SQL- would look like this:

SELECT * FROM [dbo].[Commits] 
WHERE CASE WHEN ProjectId=1 THEN (CommitsCount > 2) ELSE (Name LIKE 'R%') END

Example:

ProjectId   Name      CommitsCount
----------- --------- -----------
1           Ahmed     2
1           Ahmed     6
2           Kamel     10
3           Rami      NULL

The result I need from the query is:

ProjectId   Name      CommitsCount
----------- --------- -----------
1           Ahmed     6
3           Rami      NULL

Upvotes: 1

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Use case conditions in where clauses is not recommended. One reason is that they impede optimization of the query. Another is simply that they are not needed, so it make the conditions more complicated than necessary.

So I would recommend:

select c.*
from dbo.commits c
where (c.ProjectId = 1 and c.CommitsCount > 2) or
      (c.ProjectId <> 1 and c.Name = 'R&');

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use:

SELECT * 
FROM [dbo].[Commits] 
WHERE (CASE WHEN ProjectId  = 1 AND CommitsCount > 2 THEN 1
            WHEN ProjectId != 1 AND Name LIKE 'R%'   THEN 1
      END) = 1

db<>fiddle demo

(Name ='R&') => Name LIKE 'R%'

Upvotes: 1

Related Questions