Jack Jack - 11 months ago 41
MySQL Question

Case Statement Mysql query to Load Data based Upon Condition

I am trying with the following sql query

SELECT task.id,task.Task,task.TaskCategory,task.IsComplete,
task.AssignedDate,task.CompletionDate, task.AssignedTo,task.templateID,task.ListingID,task.ClosingID,
task.ContactID,task.LeadID,task.statusid, taskcategory.taskCategoryID,taskcategory.Category FROM
task INNER JOIN taskcategory ON task.TaskCategory = taskcategory.taskCategoryID WHERE 1=1
and (CompletionDate = CURDATE() OR CompletionDate IS NULL AND task.IsComplete = 0)
and assignedto = 2345 and id = 2421 and
CASE(WHEN status_for_design = 0 THEN (leadid is not null or ContactID is not null)
THEN NULL
END AS status_for_design)
group by
assignedto ORDER BY task.AssignedDate ASC


Main Purpose is if the status_for_design = 0 [use leadid or contactid is not null, but if the status_for_design = 1

but i am facing an error

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN status_for_design = 0 THEN (leadid is not null or ContactID is not null)
T' at line 7

Answer Source

Really, formatting your query so humans can read it would help you understand what is going on.

Your WHERE clause has an AS in it. That is not appropriate. I suspect the query you want looks more like this:

SELECT t.*, tc.taskCategoryID, tc.Category
FROM task t INNER JOIN
     taskcategory tc
     ON t.TaskCategory = tc.taskCategoryID
WHERE 1 = 1 AND
      (CompletionDate = CURDATE() OR CompletionDate IS NULL AND t.IsComplete = 0) AND
      assignedto = 2345 AND
      id = 2421 AND 
      (status_for_design <> 0 or leadid is not null or ContactID is not null)
GROUP BY assignedto
ORDER BY t.AssignedDate ASC ;

Note that the CASE is not necessary in the WHERE. I am also highly suspicious of queries that have unaggregated columns in the SELECT that are not in the GROUP BY.