Select multiple columns using single if condition

Question

I have tried the below query but somehow it's not working for me. Can anyone tell me why?

Query :

SELECT column1
     , column2
     , IF(start <="20:00:00" AND end >= "20:00:00")
          THEN (column3,column4,column5)
          ELSE (column6,column7,column8)
       END IF
     , column9
     , column10
FROM table_1
WHERE id ="1"

https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query this might help you — aPugLife, Sep 20 '17 at 14:03
Possible duplicate of [How do write IF ELSE statement in a MySQL query](https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query) — waka, Sep 20 '17 at 14:04
@PeterAbolins : Thanks for reply,I am trying to fetch multiple columns based on single if condition but somehow it's not allow me to do it. — Ankit Doshi, Sep 20 '17 at 14:05
@waka This question have single value while i want to select multiple columns.I know how to use condition for single column but don't know how to make with multiple columns — Ankit Doshi, Sep 20 '17 at 14:06

score 3 · Answer 1 · 2017-09-20T14:09:45.873

3

This syntax is incorrent, you can't use if this way.

But you can do it this way:

select 
  column1,column2, column3,column4,column5,column9,column10 
from table 1 
where id ="1" AND (start <="20:00:00"  AND end >= "20:00:00")
UNION ALL
select 
  column1,column2, column6,column7,column8,column9,column10 
from table 1 
where id ="1" AND (start >= "20:00:00"  AND end <= "20:00:00");

However, in order for this to work correctly, the columns column3,column4,column5 and column6,column7,column8 should be of the same data type respectively. Otherwise, you might need to cast them into one data type. (Thanks for the note @Peter Abolins)

edited Sep 20 '17 at 14:09

answered Sep 20 '17 at 14:04

1

It might make sense to point out that columns 3, 4 and 5 need to have identical types to columns 6, 7 and 8 correspondingly, or the above will fail. – Peter Abolins Sep 20 '17 at 14:07
@PeterAbolins - You are correct, `UNION` will fail if the types are different. Thanks for the note. I will update the answer. – Sep 20 '17 at 14:08
@DarwinThorburn : I have tried this is not working in my case. – Ankit Doshi Sep 21 '17 at 04:49
I need something like if condition is not going to satisfied then it must need to provide me result of else condition but you have put both condition in opposite way in both queries – Ankit Doshi Sep 21 '17 at 04:50

score 0 · Answer 2 · answered Sep 20 '17 at 14:10

That isn't how if statements work in mysql.

In mysql it would be more like

if(some condition, is true return this, is false return this)

so

if(1 = 1, do this, else do this)

You can chain them as well so

if(1 = 1, if(2 = 2, do this, else this), else this)

It can be pretty confusing if you come from a programming background, but I think you should check out the mysql docs on control flow functions.

Check out this answer on stack overflow for a better idea of what you're trying to accomplish. I think you're better off with case statements but an if could work as well. Best of luck!

score 0 · Answer 3 · answered Jul 28 '19 at 14:12

I've came from the future after battling with this issue. For whom it may concern and for own reasons objects using UNION statements, you might as well use JSON_OBJECT statements:

SELECT column1
     , column2
     , (IF(start <="20:00:00" AND end >= "20:00:00"),
          JSON_OBJECT("column3", column3,
                      "column4", column4,
                      "column5", column5
                      ) as result,
          JSON_OBJECT("column6", column6
                      "column7", column7
                      "column8", column8
                      ) as result,
     , column9
     , column10
FROM table_1
WHERE id ="1"

Support for JSON operators can be found after MySQL 5.7. https://dev.mysql.com/doc/refman/5.7/en/json.html

Select multiple columns using single if condition

3 Answers3