SQL nested query with join operations

Question

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

person1id	person2id
p1_1	p2_1
p1_2	p2_2

table J

personId	name
p1_1	name1
p1_2	name2
p2_1	name3
p2_2	name4

The expected result looks like

person1id	person2id	name1	name2
p1_1	p2_1	name1	name3
p1_2	p2_2	name2	name4

What's your dbms?

D-Shih
– D-Shih

2022-02-08 00:58:16 +00:00
Commented Feb 8, 2022 at 0:58 — D-Shih
– D-Shih, Commented Feb 8, 2022 at 0:58
@D-Shih presto sql

Kun Hu
– Kun Hu

2022-02-08 01:08:30 +00:00
Commented Feb 8, 2022 at 1:08 — Kun Hu
– Kun Hu, Commented Feb 8, 2022 at 1:08

D-Shih · Accepted Answer · 2022-02-08 01:05:33Z

1

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;

person1id	person2id	name1	name2
p1_1	p2_1	name1	name3
p1_2	p2_2	name2	name4

View on DB Fiddle

answered Feb 8, 2022 at 1:05

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kun Hu Over a year ago

Thanks!. I added some clarification - "In the result, name1 is the name of person1id, name2 is the name of person2id.". Is it possible to achieve that without hardcoding the person ids?

Kun Hu Over a year ago

I added an answer that seems to work.

Kun Hu · Accepted Answer · 2022-02-08 22:18:25Z

0

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

Another one with left join.

SELECT person1id,   
       person2id,
       j1.name as name1,
       j2.name as name2
FROM P
LEFT JOIN J j1 ON person1id = j1.personId
LEFT JOIN J j2 ON person2id = j2.personId

edited Feb 8, 2022 at 22:18

answered Feb 8, 2022 at 1:17

Kun Hu

4375 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQL nested query with join operations

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related