Conditional Joins

I recently came across a problem where I needed to join a table based on whether the a row in another column contained a flag.  I needed to retrive a date field from either a sale table or order table depending on status of the order.  I needed to join the tables as there was certain other data I need to take into account. 

Unfortunately SQL doesn;t support a case join statement so I went off to serch for a solution. Jeffs blog revealed a good solution involving left joining both tables then using COALESCE to get the relevant date.  I use a where statement on the status table so that I only get the data from the order table when the status is not sold and from the sale table when the item has been sold.

select
  C.CustomerName, coalesce(s.date,o.date) as ActivityDate
from
  Customers C
left outer join 
  Sales S on ...
left outer join
  Orders O on ...
where
  O.Dateis not null OR S.Date is not null

The solution worked well and, as in Jeffs blog, I could have use CASE instead of the COALESCE but I went with COALESCE for neatness.