CODEWITHSUNDEEP
databasepostgresqlviewpgadmin-4
julien carax
julien carax

Reputation: 335

Reading a column value from more than one table based on some condition - postgreSQL

I am trying to create a view for one of the tables I have. For one of the columns in the view, I am trying to read the value from the tables I have:

Table A:

id   b_id   c_id    d_id
1     1
2            1
3                     1

Table B:

id   code
1     64

Table C:

id    code
1     98

Table D:

id     code
1       26

In the above tables, A is my main table which has two foreign key references to tables B and C(b_id and c_id) respectively. And for each row in table A, only one of the two references (b_id or c_id) will be populated.

My task is to create a view for table A, say v_A, which will have the following columns:

View v_A:

code      code_table
64           B
98           C
26           D

In the above view, I have to first check for each row in table A, which of the foreign key references are populated, based on that I have to read the column 'code' and the column 'code_table' will have the name of the table I am populating the 'code' column.

I have been able to create simple views but this is a bit tricky for me. I was checking if I can use case ...when for the same but after a point that became a bit complicated too. I also can read the table name in postgres from the information_schema table. So I have bits and pieces of the solution but do not understand how to put it all together.

Can someone point me in the right direction?

Thanks!!

Upvotes: 0

Views: 78

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51456

Of course it will work only when you have EITHER one table filled OR the OTHER - it does not solve conflict when both tales have matching keys:

t=# create view _a as select coalesce(b.code,c.code,d.code) code, case when b.id is not null then 'B' when c.id is not null then 'C' when d.id is not null then 'D' end from a
left outer join b on b.id = b_id
left outer join c on c.id = c_id
left outer join d on d.id = d_id;
CREATE VIEW
t=# select * from _a;
 code | case
------+------
   26 | D
   98 | C
   64 | B
(3 rows)

Upvotes: 1

Related Questions