Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using an IF Statement in a MySQL SELECT query

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 > 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?

like image 520
Santosh Avatar asked Dec 09 '11 11:12

Santosh


People also ask

Can we use if statement in SELECT query in MySQL?

Answer: MySQL IF() function can be used within a query, while the IF-ELSE conditional statement construct is supported to be used through FUNCTIONS or STORED PROCEDURES.

Can we use if statement in SELECT query in SQL?

Both IIF() and CASE resolve as expressions within a SQL statement and can only be used in well-defined places. The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

Can you put an if statement in a query?

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.


1 Answers

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 ... 
like image 64
davidethell Avatar answered Sep 21 '22 20:09

davidethell