MySQL IF in WHERE condition Based On SELECT Column

Question

I have this simple (actually I'm simplifying the real case) SQL command :

SELECT Username, Message
FROM Mail
WHERE DeleteBySender = '0'
LIMIT 1

but for some reason, I have special condition, that is : IF Mail.Username = 'johndoe' then the WHERE clause is not DeleteBySender = '0' anymore, but DeleteByReceiver = '0'.

in short, how to make dynamic WHERE clause based on Mail.Username result?

can it be done with single SQL command? because if not, in my real case, it will cause PHP scripting more complex.

**UPDATE : what I really expect, in plain English : if Mail.Username NOT johndoe, SQL command will be like this :

SELECT Username, Message
FROM Mail
WHERE DeleteBySender = '0'
LIMIT 1

but if Mail.Username IS johndoe, SQL command will be like this :

SELECT Username, Message
FROM Mail
WHERE DeleteByReceiver = '0'
LIMIT 1

Jason McCreary · Accepted Answer · 2013-01-24 20:27:59Z

5

A solution in SQL utilizing an OR clause to break up your logic as follows:

SELECT Username, Message
FROM Mail
WHERE (Username != 'johndoe' AND DeleteBySender = '0')
OR (Username = 'johndoe' AND DeleteByReceiver = '0')
LIMIT 1

Note: The LIMIT 1 is concerning. Without knowing your dataset, this may return more than one result. You need to test.

edited Jan 24, 2013 at 20:27

answered Jan 24, 2013 at 19:37

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

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

4 Comments

Joseph Silber Over a year ago

This'll also select rows where Username = 'johndoe' and DeleteByReceiver <> '0', but DeleteBySender = '0'. I don't think that's what the OP wants. You should change the first part of the WHERE clause to (Username <> 'johndoe' AND DeleteBySender = '0')

Jason McCreary Over a year ago

@Joseph Silber, good point. See update. And thanks for commenting with your down vote.

Joseph Silber Over a year ago

Not my DV. The OP's needs (which were since updated) hadn't been expressed clearly enough to invalidate this answer, so I didn't downvote. Thought a comment would be more constructive.

Jason McCreary Over a year ago

@Joseph Silber, fair enough. I imagine they had the same point as you.

Muhammad Raheel · Accepted Answer · 2013-01-24 19:39:12Z

4

SELECT 
    Username, 
    Message
FROM Mail
WHERE (IF(Username='johndoe',DeleteByReceiver = 0,DeleteBySender =0))
LIMIT 1

answered Jan 24, 2013 at 19:39

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Comments

dnagirl · Accepted Answer · 2013-01-24 19:39:27Z

0

SELECT Username, Message
FROM Mail
WHERE (Username = 'johndoe' AND DeleteByReceiver='0') OR DeleteBySender = '0'
LIMIT 1

answered Jan 24, 2013 at 19:39

dnagirl

20.5k13 gold badges86 silver badges127 bronze badges

Collectives™ on Stack Overflow

MySQL IF in WHERE condition Based On SELECT Column

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related