How to join attributes to a many-to-many tables keys?

user_15907567 0
Viewed 38

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.

postgresql sql
1 Answers

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

user_51935360
Related