Using If Else in Mysql

Question

SELECT process.process_name, com_jobcard.job_card_num, 

IF process.UOM =  '1' THEN  SET (com_jobcard.dept_qty) AS Total_qty

ELSEIF process.UOM =  '2' THEN  SET (com_jobcard.total_stones) AS Total_qty


FROM timer_completed
INNER JOIN PROCESS ON process.id = timer_completed.process_id
INNER JOIN com_jobcard ON timer_completed.job_card_id = com_jobcard.id
AND timer_completed.report_date =  '2015-09-15'

Hi friends I would like to retrieve the value of department quantity When process.UOM = 1 else if process.UOM = '2' THEN SET (com_jobcard.total_stones) AS Total_qty , please help me with the code , thanks in advance

http://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query — S_Lord, Sep 16 '15 at 08:01

score 0 · Accepted Answer · answered Sep 16 '15 at 08:01

You can use case-when something as

SELECT 
process.process_name, 
com_jobcard.job_card_num, 
case 
 when process.UOM =  '1' then com_jobcard.dept_qty 
 when process.UOM =  '2' then com_jobcard.total_stones
end as Total_qty
FROM timer_completed
INNER JOIN PROCESS ON process.id = timer_completed.process_id
INNER JOIN com_jobcard ON timer_completed.job_card_id = com_jobcard.id
AND timer_completed.report_date =  '2015-09-15'

NOTE : Right now there is no else so its better to add an else if both condition fails

 case 
     when process.UOM =  '1' then com_jobcard.dept_qty 
     when process.UOM =  '2' then com_jobcard.total_stones
     else 'something you want'
   end as Total_qty

score 0 · Answer 2 · answered Sep 16 '15 at 09:19

IF(condition, true_value, false_value) can be used this way

SELECT process.process_name, com_jobcard.job_card_num, 

IF(process.UOM =  '1', com_jobcard.dept_qty, IF(process.UOM =  '2', com_jobcard.total_stones, 0)) AS Total_qty

FROM timer_completed
INNER JOIN PROCESS ON process.id = timer_completed.process_id
INNER JOIN com_jobcard ON timer_completed.job_card_id = com_jobcard.id
AND timer_completed.report_date =  '2015-09-15'

Using If Else in Mysql

2 Answers2