Select a row using a foreign key in another table

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?

Answer 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!