Edit Question - KnowledgeHub

Title Body

Supposed I have the following tables -- smallville=# create table contacts (name varchar(16), address_id int); CREATE TABLE smallville=# create table addresses (address_id int, address varchar(16)); CREATE TABLE smallville=# create table partners (name1 varchar(16), name2 varchar(16)); CREATE TABLE smallville=# insert into contacts values ('Clark Kent', NULL), ('Loise Lane', 1); INSERT 0 2 smallville=# insert into addresses values (1, 'Manhattan'), (2, 'North Pole'); INSERT 0 2 smallville=# insert into partners values ('Clark Kent', 'Loise Lane'), ('Loise Lane', 'Clark Kent') ; INSERT 0 2 I can get names and addresses -- smallville=# select c.name, a.address from contacts c left outer join addresses a on c.address_id = a.address_id ; name | address ------------+----------- Clark Kent | (NULL) Loise Lane | Manhattan (2 rows) But how do I get the following, i.e., show his/her partner's address if one's address is missing? -- name | address ------------+----------- Clark Kent | Manhattan Loise Lane | Manhattan (2 rows) Thanks.

Tags (comma-separated) Cancel