How to find product by 2 properties postgres

Question

I'm new in databases

I have 3 tables (columns are written for example):

• Product (id, title)
• Property (id, name, value)
• ProductProperty (product_id, property_id)

For example i have: Product

 id:    title: 
 1      "Iphone 12"
 2      "Iphone 13"
 3      "Iphone 11"

Property

 id:    name:                   value:
 1      "Housing materials"     "metal"
 2      "Housing materials"     "glass"

ProductProperty

 product_id:    property_id:
 1              1
 1              2
 2              1
 2              2
 3              1

So, the iphone 12 and iphone 13 have two properties, but the iphone 11 have only one How can i select all the iphones which have properties with id 1 and 2?

I just need to know how can i do that or not, or maybe someone can give me an advice how to design my database to made that kind of request?

Answer 1

Start with Property.

Inner join to ProductProperties twice, once for each required property value.

Because inner join requires everything to be satisfied to return rows, this should only return the stuff where the product has both properties.

Finally, dedupe the result set with GROUP BY.

SELECT 
    p.id, p.name
FROM
   Product p
-- Create a join onto ProductProperty specifically discriminating
-- on the product_id and the property_id being 1.
INNER JOIN
   ProductProperty pp1
ON p.id = pp1.product_id
AND pp1.property_id = 1
-- Then create another join, this time looking for
-- matching product_id and a product_id of 2
INNER JOIN
   ProductProperty pp2
ON p.id = pp2.product_id
AND pp2.property_id = 2
-- This *could* produce loads of rows, so ...
-- Dedupe with GROUP BY
GROUP BY
   p.id,
   p.name