how can I write IF statement in mysql

Question

I have a table with type field.

how can I check this field in query:

I want to check it if type == 'a'

then bes=amount,bed = '-'

ELSE bed=amount,bes = '-'

bes and bed is not really exists in my table but amount is (int)

this is my try:

SELECT billing.*,
    CASE billing.type WHEN 'A' THEN billing.amount AS bes, '-' AS bed ELSE billing.amount AS bed, '-' AS bes END
    FROM billing

... my searches wasn't useful

any solution...

As you can see, we are all very much in agreement about the proper solution here :-) — Jeff Rosenberg
– Jeff Rosenberg, Commented Mar 11, 2013 at 13:38
thats good... when I try this in separate query, there is no problem but in my project ... error — Pooya
– Pooya, Commented Mar 11, 2013 at 13:59

John Woo · Accepted Answer · 2013-03-11 13:32:06Z

8

You can create condition for every row, MySQL supports inline IF statements.

SELECT billing.*,
       IF(billing.type = 'A', billing.amount, '-') AS bes,
       IF(billing.type = 'A', '-', billing.amount) AS bed
FROM   billing

answered Mar 11, 2013 at 13:32

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

Jeff Rosenberg · Accepted Answer · 2013-03-11 13:59:30Z

4

You'll need two case statements for that:

SELECT billing.*,
  CASE billing.type WHEN 'A' THEN billing.amount ELSE '-' AS bes END,
  CASE billing.type WHEN 'A' THEN '-' ELSE billing.amount AS bed END
FROM billing

edited Mar 11, 2013 at 13:59

answered Mar 11, 2013 at 13:30

Jeff Rosenberg

3,5821 gold badge21 silver badges38 bronze badges

Comments

ppeterka · Accepted Answer · 2013-03-11 13:29:33Z

3

One CASE-WHEN-THEN-ELSE can have only one return value...

This should do the trick:

SELECT billing.*,
CASE billing.type WHEN 'A' THEN billing.amount ELSE '-' END AS bes,
CASE billing.type WHEN 'A' THEN '-' ELSE billing.amount END AS bed 
FROM billing

answered Mar 11, 2013 at 13:29

ppeterka

20.8k6 gold badges67 silver badges80 bronze badges

Comments

PM 77-1 · Accepted Answer · 2013-03-11 13:31:15Z

3

SELECT billing.*,
    CASE  WHEN billing.type =  'A' THEN billing.amount ELSE 0 END AS bes,
    CASE  WHEN billing.type <> 'A' THEN billing.amount ELSE 0 END AS bed
FROM billing

answered Mar 11, 2013 at 13:31

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Comments

Aiias · Accepted Answer · 2013-03-11 13:55:14Z

2

Depending on your MySQL version, you can use IF or one of two different syntaxes for CASE CASE(1), CASE(2).

SELECT *,
  (IF type = 'A' THEN amount ELSE '-' END IF) bes,
  (IF type = 'A' THEN '-' ELSE amount END IF) bed
FROM billing

answered Mar 11, 2013 at 13:55

Aiias

4,7481 gold badge20 silver badges34 bronze badges

Collectives™ on Stack Overflow

how can I write IF statement in mysql

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related