CODEWITHSUNDEEP
mysqlsql
Marten Sytema
Marten Sytema

Reputation: 1926

Match on multiple rows in child tables with MySQL

I have a table called Product, and a child table called ProductProperty:

Product(Product_id, productName,...)
ProductProperty(id, propertyKey, propertyValue, Product_id)

Thus, a product can have multiple corresponding ProductProperty rows.

Now I want to query something along the lines of: 'select all product rows that have a property width=12 and height=35'. For this particular example I came up with the following query, which works, but I feel I might be missing a good solution. 

SELECT Product.Product_id from Product 
WHERE
    EXISTS  (SELECT * FROM ProductProperty WHERE propertyKey='width'  AND propertyValue='12' AND ProductProperty.Product_id = Product.Product_id)
     AND 
    EXISTS  (SELECT * FROM ProductProperty WHERE propertyKey='height'  AND propertyValue='35' AND ProductProperty.Product_id = Product.Product_id);

Are there any obviously better implementations that I am missing here?

Upvotes: 0

Views: 49

Answers (1)

shmosel
shmosel

Reputation: 50756

How about this:

SELECT p.Product_id
  FROM Product p
  JOIN ProductProperty pp ON p.Product_id = pp.Product_id
 WHERE (pp.propertyKey = 'width' AND pp.propertyValue = '12')
    OR (pp.propertyKey = 'height' AND pp.propertyValue = '35')
 GROUP BY p.Product_id
HAVING COUNT(*) = 2;

This of course assumes that a product cannot have duplicate keys. If that is a possibility, you can try this instead:

SELECT p.Product_id
  FROM Product p
  JOIN ProductProperty pp ON p.Product_id = pp.Product_id
 GROUP BY p.Product_id
HAVING SUM(pp.propertyKey = 'width' AND pp.propertyValue = '12') > 0
   AND SUM(pp.propertyKey = 'height' AND pp.propertyValue = '35') > 0;

Upvotes: 2

Related Questions