CODEWITHSUNDEEP
databasepostgresqlforeign-keysrelational-database
Omeed Totakhel
Omeed Totakhel

Reputation: 487

reference to foreign key row postgresql

Assume we have two tables, member and company, both tables have id as primary key. If I set a column company_id in the member table as a foreign key, on the select query the column presents an integer value.

how can I make it possible to get access to the other columns in the company table such as name, phone, and email?

Option 1: Can we somehow get object/array of the referenced record? example: copmany_id_1[name, phone, email]

Option 2: Can we access it through company_id.name or company_id.phone?

Option 3: Can we get the list of foreign key columns and their referenced table and column and fire another query to get the result? this will be much slower if the table contains many foreign keys.

Upvotes: 2

Views: 1624

Answers (1)

Supun Amarasinghe
Supun Amarasinghe

Reputation: 1443

I am assuming that accessing means selecting the rows of the relevant table.You can use a join to access the columns in the other table.

SELECT c.company_id,c.company_name, c.company_phone,m.member_id
FROM company c
INNER JOIN member m
ON member.company_id = company .company_id;

If you want to do modifications to the company table columns you can do this by selecting it like this.

Upvotes: 1

Related Questions