CODEWITHSUNDEEP
sqlpostgresqlselectcase
JamesStandbridge
JamesStandbridge

Reputation: 41

Postgresql Select a column or other under the same alias according to the value of a third column

I have a database whose schema can be simplified in this way: a product table, and two tables containing values to enrich the description of a product. I would like to make a SELECT query allowing me to display the product id followed by a column containing a value. This value can be selected in the attribute_value_1 table if the product is of type 'Type1' or in the attribute_value_2 table if the product is of type 'Type2'.

Table product

product_id type
1 type1
2 type2

Table attribute_value_1

id product_id value
1 1 XXXXX

Table attribute_value_2

id product_id value
1 2 ZZZZZ

So in the following example, the result of the query should be :

Result

product_id value
1 XXXXX
2 ZZZZZ

Do you know how I can proceed?

Upvotes: 1

Views: 469

Answers (1)

eshirvana
eshirvana

Reputation: 24593

you can use two left joins with each attribute table and then using coalesce() function , get the value from those table thatmatched:

select P.product_id , coalesce(att1.value, att2.value) value
from product p 
left join attribute_value_1 att1
   on p.product_id = att1.product_id and p.type = 'type1'
left join attribute_value_1 att2
   on p.product_id = att2.product_id and p.type = 'type2'

Upvotes: 1

Related Questions