Using an IF Statement in a MySQL SELECT query

Question

I am trying to use an IF statement in a MySQL select query     I am getting an error after the AND statement where the first IF   SELECT J JOB ID E COMPANY NAME J JOB DESC JT JOBTYPE NAME J COMPENSATION ST STATE NAME MC METRO CITY NAME I INDUSTRY NAME   J JOB CONTACT PERSON J DT INSRT J JOB TITLE J JOB EXP DATE J SKILLS  FROM JOBS J   JOIN EMPLOYER E ON J COMPANY ID E COMPANY ID   JOIN LOOKUP JOBTYPE JT ON J JOB TYPE JT JOBTYPE ID  JOIN LOOKUP STATE ST ON J STATE ID ST STATE ID  JOIN JOBS LOCATION JL ON J JOB ID JL JOB ID  JOIN LOOKUP METRO CITY MC ON JL METRO CITY ID MC METRO CITY ID  JOIN LOOKUP INDUSTRY I ON J INDUSTRY ID I INDUSTRY ID   JOIN JOBS QUALIFICATION JQ ON J JOB ID JQ JOB ID   JOIN LOOKUP DEGREE QUALIFICATION LDQ ON LDQ QUALIFICATION ID   JQ QUALIFICATION ID  WHERE  J ACTIVE  Y  AND J DT INSRT  gt  COALESCE pEmailSntDt DATE SUB SYSDATE   INTERVAL 4 DAY     AND IF JQ COURSE ID 0   THEN IF JQ DEGREE ID 0  THEN J SKILLS LIKE CONCAT      pSkills      ELSE JQ DEGREE ID pDegreeId OR J SKILLS LIKE CONCAT      pSkills      END IF ELSE JQ COURSE ID pCourseId OR IF JQ DEGREE ID 0                        THEN                       J SKILLS LIKE CONCAT      pSkills                            ELSE                       JQ DEGREE ID pDegreeId OR J SKILLS LIKE CONCAT      pSkills                            END IF END IF                            GROUP BY J JOB ID ORDER BY J DT INSRT DESC    Why doesn t this work and what is the proper way to do an IF statement in a MySQL query

User · Accepted Answer

The IF THEN ELSE construct you are using is only valid in stored procedures and functions  Your query will need to be restructured because you can t use the IF   function to control the flow of the WHERE clause like this    The IF   function that can be used in queries is primarily meant to be used in the SELECT portion of the query for selecting different data based on certain conditions  not so much to be used in the WHERE portion of the query   SELECT IF JQ COURSE ID 0   Some Result If True    Some Result If False    OTHER COLUMNS FROM     WHERE

User · Answer

How to use an IF statement in the MySQL  select list    select if  1 gt 2  2  3                             returns 3 select if 1 lt 2  yes   no                          returns yes SELECT IF STRCMP  test   test1    no   yes       returns no   How to use an IF statement in the MySQL where clause search condition list   create table penguins  id int primary key auto increment  name varchar 100   insert into penguins  name  values   rico   insert into penguins  name  values   kowalski   insert into penguins  name  values   skipper    select   from penguins where 3   id -- gt 3    skipper  select   from penguins where  if  true  2  3     id -- gt 2    kowalski   How to use an IF statement in the MySQL  having clause search conditions    select   from penguins  where 1 1 having  if  true  2  3     id -- gt 1    rico   Use an IF statement with a column used in the select list to make a decision   select  if  id   2  -1  1   item from penguins where 1 1 -- gt  1 -- gt  -1 -- gt  1   If statements embedded in SQL queries is a bad  code smell    Bad code has high  WTF s per minute  during code review   This is one of those things   If I see this in production with your name on it  I m going to automatically  not like you

User · Answer

try this code worked for me  SELECT user display image AS user image         user display name AS user name         invitee phone          CASE WHEN invitee status   1 THEN  attending               WHEN invitee status   2 THEN  unsure               WHEN invitee status   3 THEN  declined               WHEN invitee status   0 THEN  notreviwed         END  AS invitee status   FROM your table

[mysql] Using an IF Statement in a MySQL SELECT query

Examples related to mysql

Examples related to if-statement

Examples related to select-query