Database Relation source depending on field

Question

I have a database of projects and their parent companies that manage them, each project and company has lists of departaments and countries. However if project has field DEFAULT set to true, the list of dep/ countries should be loaded from its parent company instead.

My question is what would be the best method to design this ? Is it possible to do it "correctly" in MySQL or should i just manage it in application code (not too good).

Answer 1

You can use a conditional expression in your join criteria. For example, using MySQL's IF() function:

JOIN departments ON departments.departmentID = IF(projects.default,
    company_departments.departmentID,
   projects_departments.departmentID
)