Q:

Postgresql select two correlated rows in a table and shows the opposite of the correlated field into another new column from the output result

I have a table table1 as shown below:
+--------+----------------+---------+
| userid | correlated_id  | type    |
+--------+----------------+---------+
| user_1 | abc-123        |husband  |
| user_2 | abc-123        |wife     |
| user_1 | def-456        |solo     |
| user_1 | ghi-789        |brother  |
| user_3 | ghi-789        |sister   |
+--------+----------------+---------+

I would like my select query to be filtered by userid = 'user_1' and type = 'husband' as well as create another column named related_userid from the query as shown below:
+--------+----------------+---------+----------------+
| userid | correlated_id  | type    | related_userid |
+--------+----------------+---------+----------------+
| user_1 | abc-123        | husband | user_2         |
+--------+----------------+---------+----------------+

I would also like the query to be as efficient as possible. Thanks!

A:

Make use of self join  to get the desired result:
SELECT a.userid, a.correlated_id, a.type, b.userid AS related_userid
FROM   table1 a
JOIN   table1 b ON a.correlated_id = b.correlated_id
where a.userid = 'user_1' and a.type = 'husband' and a.userid <> b.userid;

