Select a row using a foreign key in another table

by

The question:

Hi I’m using python and sqlalchemy and postgresql for creating a relational database

I have 2 tables
Users and Connections
Users have information about each user with their id as primary-key
and Connections have their id (Users.id) as Foreign-key
Imagine I add multiple user id into Connections
and after a few moments I want to select a row where the user have specific information in their Users table row

for example :

Table Users :
    Column id BIGINT primary key
    Column age INTEGER

Table Connections:
    Column user_id BIGINT ForeignKey(Users.id)
    Column Connection_id VARCHAR(100)

SELECT id FROM Connections WHERE Users(Connections.id).age = 20 limit 1;

what should i do?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Firstly (although it doesn’s apply in this case), you should always include your version of PostgreSQL with any questions here.

What you are referring to is an [INNER] JOIN. The different types of joins are explained here. There are many links on different join types out there.

So, in this case, I would do the following:

CREATE TABLE my_user 
(
  id INT  NOT NULL, 
  age INT NOT NULL, 
  PRIMARY KEY (id)
);

and

CREATE TABLE connection 
(
  connection_id INT NOT NULL, 
  user_id       INT NOT NULL, 
  PRIMARY KEY (connection_id),
  CONSTRAINT conn_user_fk FOREIGN KEY (user_id) REFERENCES my_user (id)
);

populate them:

INSERT INTO my_user VALUES (1, 50), (2, 45), (3, 73);

and

INSERT INTO connection (user_id, connection_id)
VALUES 
(1, 500), (1, 501), (1, 502), (1, 503), (1, 504),
(2, 601), (2, 602), (2, 603), (2, 604), (2, 605),
(3, 701), (3, 702), (3, 703), (3, 704);

And then run the following SQL:

SELECT 
  u.id, u.age, c.connection_id, c.user_id
FROM my_user u
JOIN connection c
  ON u.id = c.user_id;

Result:

id  age     connection_id   user_id
1   50                500   1
1   50                501   1
1   50                502   1
1   50                503   1
1   50                504   1
2   45                601   2
2   45                602   2
2   45                603   2
2   45                604   2
2   45                605   2
3   73                701   3
3   73                702   3
3   73                703   3
3   73                704   3

So, you have all the information from both tables. If you have more fields, you may wish to include or exclude them as per your requirements.

One word of strong advice. Do NOT store people’s ages. Rather, store their birthday and calculate their age from that on the fly!

For example:

CREATE TABLE my_user_bis
(
  id INTEGER, 
  bday TIMESTAMPTZ
);

1 record as a sample:

SELECT 
  *, 
  AGE(CURRENT_DATE, bday::DATE),
  EXTRACT(YEAR from AGE(bday))
FROM my_user_bis;

Result:

id        bday             age                          date_part
1   2000-05-05 00:00:00+01  21 years 11 mons 16 days    21

If you don’t store birthdays and do a calculation on the fly, you’ll start to get errors – what happens if someone who’s 53 now record is accessed in 3 years time – it’ll still show him as 53. Unless you implement some sort of horrible hack with a trigger running at the end of each year! Yuck! My advice is to store birthdays – if the user is happy to provide their age, there’s no reason that they wouldn’t provide a birthday!

Welcome to dba.se!


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment