0

I am new to postgresssql. I have two table like this

Table A

id      |value  | type1  | type2  | type3
bigint  |text   | bigint | bigint | bigint

Table B

Id      | description
bigint  | text

Table A's type1,type2,type3 is the ids of Table B but not foreign key constraint.

I have to retrieve like this

Select a.id,
       a.value,
       b.description1(as of a.type1),
       b.description1(as of a.type1),
       b.description1(as of a.type1)
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
madhu
  • 51
  • 1
  • 3

2 Answers2

1

If you have to many columns you should consider change your db design

TableA

   id   | value        
    1   | <something>
    2   | <something>

TableAType

    id  | TableA.id | type_id | typeValue
     1  |     1     | type1   | bigint
     2  |     1     | type2   | bigint
     3  |     1     | type3   | bigint
           .....
     4  |     1     | typeN   | bigint

TableB (type_description)

Id      | description
bigint  | text

Then your query become more simple and isn't affected when you add/remove types.

SELECT TB.Description, TT.TypeValue
FROM TableAType TT
JOIN TableB     TB
  ON TT.Type_id = TB.id

Then you can use a PIVOT to get the tabular data. Again the advantage is you can delete remove types, and your query doesnt change, only need update the types tables.

Community
  • 1
  • 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
0

You should (LEFT) JOIN tableB 3 times, and use a different alias for each one.

select id, value, 
           type1, t1.description descri1, 
           type2, t2.description descri2,
           type3, t3.description descri3
from       tableA ta
left  join tableB t1
on         ta.type1 = t1.id 
left  join tableB t2
on         ta.type2 = t2.id 
left  join tableB t3
on         ta.type3 = t3.id;
McNets
  • 10,352
  • 3
  • 32
  • 61