reference to foreign key row postgresql

Question

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.

Is this MySQL or Postgres?

tadman
– tadman

2017-09-12 20:23:47 +00:00
Commented Sep 12, 2017 at 20:23 — tadman
– tadman, Commented Sep 12, 2017 at 20:23
It is postgres database

Omeed Totakhel
– Omeed Totakhel

2017-09-13 04:02:14 +00:00
Commented Sep 13, 2017 at 4:02 — Omeed Totakhel
– Omeed Totakhel, Commented Sep 13, 2017 at 4:02
Read up on joins

user330315
– user330315

2017-09-13 08:42:04 +00:00
Commented Sep 13, 2017 at 8:42 — user330315
– user330315, Commented Sep 13, 2017 at 8:42

Supun Amarasinghe · Accepted Answer · 2017-09-13 04:44:06Z

1

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.

answered Sep 13, 2017 at 4:44

Supun Amarasinghe

1,4433 gold badges14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

reference to foreign key row postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related