CODEWITHSUNDEEP
mysqlsqljoincase
Dunams
Dunams

Reputation: 112

MySQL join different table on case

I'm trying to join a different table depended on the value of a "task_type" column

What is wrong with the syntax? Thanks.

SELECT t.*,s.task_name
    CASE
    WHEN t.task_type = 0 THEN
    LEFT JOIN scheduler_tasks s ON s.scheduler_task_id = t.task_id
    WHEN t.task_type = 1 THEN 
    LEFT JOIN invoice_tasks s ON s.uid = t.task_id 
    END 
    FROM task_timings t 
    WHERE t.account_id = ? AND t.start_date >= DATE(?) AND t.start_date <= DATE(?)

Upvotes: 1

Views: 3409

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I believe you want this:

SELECT t.*,
       COALESCE(s.task_name, i.task_name) as task_name
FROM task_timings t LEFT JOIN
     scheduler_tasks s
     ON s.scheduler_task_id = t.task_id AND 
        t.task_type = 0 LEFT JOIN
     invoice_tasks i
     ON i.uid = t.task_id AND t.task_type = 1
WHERE t.account_id = ? AND t.start_date >= DATE(?) AND
      t.start_date <= DATE(?);

Upvotes: 2

Coskun Ozogul
Coskun Ozogul

Reputation: 2487

Can you try like this :

SELECT t.*,s.task_name FROM task_timings t 
CASE
WHEN t.task_type = 0 THEN
LEFT JOIN scheduler_tasks s ON s.scheduler_task_id = t.task_id
WHEN t.task_type = 1 THEN 
LEFT JOIN invoice_tasks s ON s.uid = t.task_id 
END 
    WHERE t.account_id = ? AND t.start_date >= DATE(?) AND t.start_date <= DATE(?)

Upvotes: 0

Related Questions