sql - Match on multiple rows in child tables with MySQL -

sql - Match on multiple rows in child tables with MySQL -

i have table called product, , kid table called productproperty:

product(product_id, productname,...) productproperty(id, propertykey, propertyvalue, product_id)

thus, product can have multiple corresponding productproperty rows.

now want query along lines of: 'select product rows have property width=12 , height=35'. particular illustration came next query, works, sense might missing solution.

select product.product_id product exists (select * productproperty propertykey='width' , propertyvalue='12' , productproperty.product_id = product.product_id) , exists (select * productproperty propertykey='height' , propertyvalue='35' , productproperty.product_id = product.product_id);

are there improve implementations missing here?

how this:

select p.product_id product p bring together productproperty pp on p.product_id = pp.product_id (pp.propertykey = 'width' , pp.propertyvalue = '12') or (pp.propertykey = 'height' , pp.propertyvalue = '35') grouping p.product_id having count(*) = 2;

this of course of study assumes product cannot have duplicate keys. if possibility, can seek instead:

select p.product_id product p bring together productproperty pp on p.product_id = pp.product_id grouping p.product_id having sum(pp.propertykey = 'width' , pp.propertyvalue = '12') > 0 , sum(pp.propertykey = 'height' , pp.propertyvalue = '35') > 0;

mysql sql