SQL query to return 2 values from different table

Question

I'm just learning SQL and cannot do one thing.

I have 2 tables:

Persons:

CREATE TABLE Persons 
(
    `id` INT,
    `name` TEXT,
    `id_region1` INT,
    `id_region2` INT,
    PRIMARY KEY (`id`)
);

INSERT INTO Persons VALUES (1, 'pp1', 1, 1);
INSERT INTO Persons VALUES (2, 'pp2', 2, 1);
INSERT INTO Persons VALUES (3, 'pp3', 2, 3);

Region

CREATE TABLE Region 
(
    `id_region` INT,
    `region_name` TEXT,
    PRIMARY KEY (`id_region`)
);

INSERT INTO Region VALUES (1, 'AAA');
INSERT INTO Region VALUES (2, 'BBB');
INSERT INTO Region VALUES (3, 'CCC');

I would like to get this result:

1 | pp1 | AAA | AAA 
2 | pp2 | BBB | AAA 
3 | pp3 | BBB | CCC

I tried to use a join, but it cannot be done to same column.

LEFT JOIN regions twice. (One time per person region.)

jarlh
– jarlh

2020-05-04 15:02:43 +00:00
Commented May 4, 2020 at 15:02 — jarlh
– jarlh, Commented May 4, 2020 at 15:02
ok but how to display that in columns?

Piotr Janiewiadomski
– Piotr Janiewiadomski

2020-05-04 15:11:18 +00:00
Commented May 4, 2020 at 15:11 — Piotr Janiewiadomski
– Piotr Janiewiadomski, Commented May 4, 2020 at 15:11

jarlh · Accepted Answer · 2020-05-04 15:14:00Z

1

LEFT JOIN region table twice. (One time per person region.)

select p.id, p.name. r1.region_name, r2.region_name
from persons p
left join region r1 on p.id_region1 = r1.id_region
left join region r2 on p.id_region2 = r2.id_region

(Do LEFT JOIN just in case, if a person has 0 or 1 region only.)

edited May 4, 2020 at 15:14

answered May 4, 2020 at 15:10

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

SQL query to return 2 values from different table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related