Determining the actual value of a record when it fails in a OR query

Question

I've got a simple query which functions correctly, but there are few conditions that need to be checked. This is being done using AND operator, I want to know how can we determine the actual value when an OR condition failed.

Scenario: We've got 3 columns is_blocked, is_verified,is_active Let's say Row 1 has

is_blocked=0
is_verified=0
is_active=0

The message thrown has to be different when each of the condition fails.

if (is_blocked==1) msg=your account has been blocked.
if (is_active==0) msg=your account is no longer active.
if (is_verified==0) msg=you need to verify your account.

Query 1 :

"
SELECT * 
  from users 
 WHERE is_blocked = 0 
   AND is_verified = 1 
   AND is_active = 0 
   AND emailid = 'emailid'
"

The above query will return the value as FALSE for the above example. Is there a way for me to figure out with a single query, as to which condition in the query has failed, and what the actual value is. So the expected output will be is_verified=1

I've tried using if condition in PHP something like this.

if (is_blocked=0) 
SELECT * from users users WHERE is_verified=1 AND is_active=1

and so on... I am not sure if this is a good practice.

Did you look at MySQL `if`? https://dev.mysql.com/doc/refman/8.0/en/if.html or `case` https://dev.mysql.com/doc/refman/8.0/en/case.html — AbraCadaver, May 17 '19 at 15:13
`SELECT is_blocked = 0, is_verified = 1, is_active = 0, emailid = 'emailid' FROM users;` — Strawberry, May 17 '19 at 15:13
@Strawberry This won't work, as I'd like the output to point out which condition in the query caused the query to fail. In this case it should say is_verified=0. So we'll know that the query failed since the value of is_verified is 0 — Vinay, May 17 '19 at 15:19
@AbraCadaver I didn't check the link, will do so now. But I believe CASE will be like if conditions, i was trying to figure out a way for the query to say the condition failed, since one of the variables had the incorrect value and the variablename is is_verified =0 I'll check the link though.. Thank you for sharing — Vinay, May 17 '19 at 15:24
@Strawberry oops, sorry about that. Thank You for your input, is what I am expecting even possible, rather was I able to explain the question ? — Vinay, May 17 '19 at 15:26
@AbraCadaver and Strawberry Thank You for your quick help. I think I was able to figure out a similar question a user had asked here ([link]https://stackoverflow.com/questions/1412195/determine-which-parts-of-where-statement-failed?rq=1) — Vinay, May 17 '19 at 15:38

score 0 · Answer 1 · answered May 17 '19 at 16:20

Use a CASE expression:

SELECT CASE 
    WHEN is_blocked = 1 THEN 'your account has been blocked'
    WHEN is_active = 0 THEN 'your account is no longer active'
    WHEN is_verified = 0 THEN 'you need to verify your account'
    END AS reason
from users
WHERE emailid = 'emailid'

Determining the actual value of a record when it fails in a OR query

1 Answers1