PostgreSQL Conditional Join
select c.name, coalesce(a.address, a1. address) from contacts cleft outer join addresses a on c.address_id = a.address_id left outer join partners on c.name=partners.name1 left outer join contacts c1 on c1.name=partners.name2 left outer join addresses a1 on c1.address_id = a1.address_id;