Jack Jack - 3 months ago 10
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

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.