CODEWITHSUNDEEP
mysqlsqljoinconditional-statements
Ravioli87
Ravioli87

Reputation: 835

MySQL Conditional "ON" Statement

I have two tables: Programs and Departments.

Each program belongs to one department. The twist is, the programs table has an optional field called override_dept_id. When this field is not null, it takes precedence over the value in the department_id field.

My query needs to be able to check that if the override_dept_id field is null or not. The result of this decides which key is used on the join of the departments table. I have tried a few different ways, but I'm not doing something correctly.

Here is what I am trying to do:

SELECT p.id, p.program, p.full_name, d.department 
FROM programs p
LEFT JOIN departments d
CASE
    WHEN p.override_dept_id IS NOT NULL THEN (ON p.override_dept_id = d.id)
    ELSE (ON p.department_id = d.id)
END
ORDER BY p.full_name ASC

Upvotes: 2

Views: 202

Answers (1)

Mureinik
Mureinik

Reputation: 311508

The on keyword must follow the joined table - but you could move the logic to the on clause itself:

SELECT    p.id, p.program, p.full_name, d.department 
FROM      programs p
LEFT JOIN departments d ON d.id = COALESCE(p.override_dept_id,  p.department_id)
ORDER BY  p.full_name ASC

Upvotes: 4

Related Questions