Q:

SQL Optional JOIN

I have the following SQL statement:
SELECT
    invoice.id         "Invoice ID",
    account.name       "Owner"
FROM
    invoice
LEFT OUTER JOIN account 
    on invoice.customerid = account.id

However, the invoice.customerid may be an account or a contact. That's determined by another field, the customertype, in the invoice table.
So how can I change my JOIN to JOIN to different tables for each row based on the value of another column?
Using MS SQL Server.

A:

I am assuming that there is another table 'contact' which you will need to join to and that you want to only return the account name or contact name depending on the value of the customertype column. I made the assumption that customertype is a string that may be either 'account' or 'contact' below:
SELECT
    invoice.id 'Invoice ID',
    CASE invoice.customertype  
      WHEN 'account' THEN account.name
      WHEN 'contact' THEN contact.name   
    END 'Owner'
FROM
    invoice
LEFT OUTER JOIN account 
    ON invoice.customerid = account.id
LEFT OUTER JOIN contact
    ON invoice.customerid = contact.id

