Mysql Query : filter with condition

Question

I have a table Orders(OID, OrderType, ODep, ReqUser)

i want to filter like below logic:

SELECT * FROM Orders if  (Ordertype='confidential') filter by ReqUser='$UserID'  else filter by ODep

i am not sure if it is possible!

Uueerdo · Accepted Answer · 2016-02-11 21:54:57Z

1

In these situations you can generally replace if a then b else c with (a AND b) OR ((NOT a) AND c), like so:

SELECT * 
FROM Orders 
WHERE (Ordertype='confidential' AND ReqUser='$UserID')
   OR (Ordertype<>'confidential' AND ODep)
;

Alternatively, depending on your data, this may work as well:

SELECT * 
FROM Orders 
WHERE ReqUser='$UserID'
   OR (Ordertype<>'confidential' AND ODep)
;

or this:

SELECT * 
FROM Orders 
WHERE ODep AND (ReqUser='$UserID' OR Ordertype <> 'confidential')
;

answered Feb 11, 2016 at 21:45

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nope, what if the requested ReqUser does not exist?

@dnoeth Then it will filter by whatever ODep evaluates to.

You might want to include an Ordertype<>'confidential' clause in your second OR condition.

@Marvin that is good point, and I would guess that is probably the more desired result... correcting.