I have a table P with person1id and person2id columns, and a table J with each person's personId and their name columns. I want to have a query that generates a table of person1id, person2id, name1, name2 columns. In the result, name1 is the name of person1id, name2 is the name of person2id. Is it possible to do this via nested query?

The tables look like below

table P

table J

The expected result looks like

CodePudding user response：

You can try to use join with condition aggregate function, the CASE WHEN condition depends on your new column logic which is your expectation.

Query #1

SELECT person1id,   
       person2id,
       MAX(CASE WHEN personId IN ('p1_1','p1_2') THEN name END) name1,
       MAX(CASE WHEN personId IN ('p2_1','p2_2') THEN name END) name2
FROM P
INNER JOIN J ON personId IN (P.person1id,P.person2id)
GROUP BY person1id, 
       person2id;

View on DB Fiddle

CodePudding user response：

Figured out a query without join.

SELECT person1id,   
       person2id,
       (SELECT name FROM J WHERE personId = person1id) AS name1,
       (SELECT name FROM J WHERE personId = person2id) AS name2
FROM P