I currently have a table called borders which functions as a many-to-many relation for two country-entities:

CREATE TABLE borders
(
    Country1 VARCHAR(4),
    Country2 VARCHAR(4),
    Length DECIMAL 
        CHECK (Length > 0),

    CONSTRAINT BorderKey PRIMARY KEY (Country1,Country2) 
);

Where each tuple in the table looks something like:

|      country1       |    country2    |    length      |
|---------------------|----------------|----------------|
|          AL         |       MK       |     3271       |
|          GR         |       TR       |     1712       |

Now I would like to create a query that projects a table where you can also see the GDP for both of these countries, gathered from the Economy table which looks like:

CREATE TABLE Economy
(
    Country VARCHAR(4) CONSTRAINT EconomyKey PRIMARY KEY,
    GDP DECIMAL CONSTRAINT EconomyGDP
        CHECK (GDP >= 0)
);

The resulting table should look something like:

|      country1       |    country2    |      GDP1      |      GDP2      |     length      |
|---------------------|----------------|----------------|----------------|------------------
|          AL         |       MK       |       428      |       734      |       3271      |
|          GR         |       TR       |       300      |       823      |       1712      |

Where GDP1 corresponds to country1 and GDP2 corresponds to country2.

CodePudding user response：

You can join the Ecomny table twice for every country

Like

CREATE TABLE borders(
  Country1 VARCHAR(4),
  Country2 VARCHAR(4),
  Length DECIMAL 
  CHECK (Length > 0),
  CONSTRAINT BorderKey PRIMARY KEY (Country1,Country2) 
);

INSERT INTO borders VALUES('A1','A2',1000)

CREATE TABLE Economy(
  Country VARCHAR(4) CONSTRAINT EconomyKey PRIMARY KEY,
  GDP DECIMAL CONSTRAINT EconomyGDP
  CHECK (GDP >= 0)
);

INSERT INTO Economy VALUES ('A1',100);

INSERT INTO Economy VALUES('A2',101);

SELECT b.Country1,b.Country2, e1.GDP as GDP1,e2.GDP AS GDP2,b.Length
FROM borders b 
JOIN Economy e1 ON b.Country1 = e1.Country
JOIN Economy e2 ON b.Country2 = e2.Country

COUNTRY1 | COUNTRY2 | GDP1 | GDP2 | LENGTH
:------- | :------- | ---: | ---: | -----:
A1       | A2       |  100 |  101 |   1000

db<>fiddle here