How do you populate boolean columns based on a table of properties?

I have a table called Product that contains a product identification number and an id associated with the product identification number.

pin id
11 10
12 11
13 12

I also have a property table with the id and property_id

id property_id
10, 108
11, 109
12, 200

108 refers to the property isNew, 109 refers to the property isPremium and 200 refers to the property isExclusive.

With these, I want to create this temporary table:

pin id isNew isPremium isExclusive
11 10 1 0 0
12 11 0 1 0
13 12 0 0 1

How do I do this? You can create a temporary table easily, but I am not sure how to map the values.

Answer

You can do conditional aggregation:

select pt.pin, pt.id,
    max(case when py.property_id = 108 then 1 else 0 end) as is_new,
    max(case when py.property_id = 109 then 1 else 0 end) as is_premium,
    max(case when py.property_id = 200 then 1 else 0 end) as is_exclusive
from product pt
inner join property py on py.id = pt.id
group by pt.pin, pt.id

This assumes that there may be several properties per product – unlike what is showed in your sample data. If that’s not the case, aggregation is not necessary:

select pt.pin, pt.id,
    case when py.property_id = 108 then 1 else 0 end as is_new,
    case when py.property_id = 109 then 1 else 0 end as is_premium,
    case when py.property_id = 200 then 1 else 0 end as is_exclusive
from product pt
inner join property py on py.id = pt.id