CODEWITHSUNDEEP
mysql
Ankit Doshi
Ankit Doshi

Reputation: 1174

Select multiple columns using single if condition

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"

Upvotes: 0

Views: 3521

Answers (3)

user7262544
user7262544

Reputation:

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

Upvotes: 0

Robbie Milejczak
Robbie Milejczak

Reputation: 5770

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!

Upvotes: 0

user8608184
user8608184

Reputation:

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)

Upvotes: 3

Related Questions