MySQL if statement subquery value

Question

I want to do something like this:

SELECT IF((SELECT something FROM table) AS tmp) > 0, tmp, (SELECT bla FROM oter_table))

Unfortunately the 'as tmp' and returing the tmp is not working. How can I get this to work? without doing repeating the query:

SELECT IF((SELECT something FROM table) > 0, (SELECT something FROM table), (SELECT bla FROM oter_table))

The actual situation is a bit more complicated, therefore I would like to know how I could fix the above situation (without joins). — user2511309
– user2511309, Commented Jul 18, 2013 at 12:50

manji · Accepted Answer · 2013-07-18 12:59:27Z

4

You can use User-Defined Variables:

SELECT IF(
          @val:=(SELECT something FROM table1) > 0, 
          @val, 
          (SELECT bla FROM table2)
       )

SQL Fiddle

answered Jul 18, 2013 at 12:59

manji

48k5 gold badges98 silver badges104 bronze badges

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

Comments

DevlshOne · Accepted Answer · 2013-07-18 13:00:30Z

0

SELECT IF( (SELECT something AS tmp FROM first_table) > 0, tmp, (SELECT bla FROM other_table) )

EDIT This is not going to work. You need to quantify the selection somewhere with a "WHERE" clause, like so:

SELECT IF( (SELECT something FROM first_table) > 0, (SELECT something FROM first_table), (SELECT something_else FROM second_table) ) FROM first_table WHERE something = 45

edited Jul 18, 2013 at 13:00

answered Jul 18, 2013 at 12:54

DevlshOne

8,4671 gold badge31 silver badges38 bronze badges

Collectives™ on Stack Overflow

MySQL if statement subquery value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related