CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Lucia Veldera
Lucia Veldera

Reputation: 33

How to know how many conditions in WHERE statement have met its criteria?

Lets say we have the following SQL statement.

SELECT ID
FROM table1
WHERE P1 = P1 AND (P2 = X OR P3 = Y OR P4 = Z)

This statement should return a result if at least two conditions in parentheses have met its criteria, but I don't know how to write a statement that gives this kind of result. Thank you in advance.

Upvotes: 1

Views: 165

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

Trivially, we can just expand your WHERE clause as follows:

SELECT ID
FROM table1
WHERE P1 = P1 AND (
                  P2 = X AND P3 = Y OR
                  P2 = X AND P4 = Z OR
                  P3 = Y and P4 = Z);

Depending on your data and the logic behind it, it might be possible to simplify the above even further.

Upvotes: 1

forpas
forpas

Reputation: 164064

Use CASE expressions for each of the conditions that return 1 or 0 and add them:

SELECT ID 
FROM table1 
WHERE P1 = P1 
  AND CASE WHEN P2 = X THEN 1 ELSE 0 END +
      CASE WHEN P3 = Y THEN 1 ELSE 0 END +
      CASE WHEN P4 = Z THEN 1 ELSE 0 END >= 2

This is scalable for more conditions.

Upvotes: 5

Related Questions