Q:

Conditional Joins/ Alternate selection

I am writing a query where I need to select either a parent company if it exists and if the parent company doesn't exist and second company exists i need to select that and if both do not exist then I need to select company (child company). 
Given that there is a Company table with ID and company name.
There is also a Movie table which has the following columns:

ID (referring to movie ID #)
parent company column referring to (ID # in the company table)
2nd company referring to (ID # in company table) 
Company (name of the child company)

How would I create a query that does that? 
Please help!!

A:

You can use a case statement in your query to match your logic. I think this should be close:
select m.id,
      case
           -- has both, use child
           when p.id is not null and s.id is not null then m.company
           -- has parent not second, use parent
           when p.id is not null and s.id is null then p.company
           -- has second not parent, use second
           when p.id is null and s.id is null then p.company
           -- has neither, not sure what you want to do
           when p.id is null and s.id is null then 'None'
      end as company
from movies as m
left join companies as s
    on m.second_company_id = s.id
left join companies as p
    on m.parent_company_id = p.id

