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"

Possible duplicate of How do write IF ELSE statement in a MySQL query — waka
– waka, Commented Sep 20, 2017 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
– Ankit Doshi, Commented Sep 20, 2017 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
– Ankit Doshi, Commented Sep 20, 2017 at 14:06

score 3 · Accepted Answer · 2017-09-20 14:09:45Z

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, 2017 at 14:09

answered Sep 20, 2017 at 14:04

user8608184

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

4 Comments

Peter Abolins Over a year ago

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.

user8608184 Over a year ago

@PeterAbolins - You are correct, UNION will fail if the types are different. Thanks for the note. I will update the answer.

Ankit Doshi Over a year ago

@DarwinThorburn : I have tried this is not working in my case.

Ankit Doshi Over a year ago

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

Robbie Milejczak · Accepted Answer · 2017-09-20 14:10:42Z

0

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!

answered Sep 20, 2017 at 14:10

Robbie Milejczak

5,8203 gold badges37 silver badges68 bronze badges

Comments

user7262544 · Accepted Answer · 2019-07-28 14:12:10Z

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

Collectives™ on Stack Overflow

Select multiple columns using single if condition

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

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

Linked

Related