SQL query on join table

Question

I've the follow SQL schema:

+----------+
| products |
+----------+
| id       |
| name     |
+----------+
    ^ 8
    |
    v 1
+-------------+
|   values    |
+-------------+
| value       |
| product_id  |
| property_id |
+-------------+
    ^ 8
    |
    v 1
+------------+
| properties |
+------------+
| id         |
| name       |
+------------+

One product has many properties and a property belongs to many products. The values table is the join table for the many_to_many association between products and properties. And in this table is saved the value of the property for a product.

Now I'm looking for a query to select all products with property x with value a, and property y with value b ecc. My try is this query but return no records:

SELECT DISTINCT 
    products.* 
FROM 
    products 
INNER JOIN 
    product_values 
    ON product_values.product_id = products.id 
INNER JOIN 
    properties 
    ON properties.id = product_values.property_id 
WHERE 
    (properties.name = 'size' AND product_values.value = 'big') 
    AND (properties.name = 'color' AND product_values.value = 'red')

If possible I need a query with no nested select.

juergen d · Accepted Answer · 2017-11-28 13:35:08Z

4

Since a property can not be color and size at the same time you need to use OR in your where clause. Then group the data and check if both are in the group with having

SELECT products.id, products.name
FROM `products` 
INNER JOIN `product_values` ON `product_values`.`product_id` = `products`.`id` 
INNER JOIN `properties` ON `properties`.`id` = `product_values`.`property_id` 
WHERE (properties.name = 'size' AND product_values.value = 'big') 
   OR (properties.name = 'color' AND product_values.value = 'red')
GROUP BY products.id, products.name
HAVING count(distinct properties.name) = 2

edited Nov 28, 2017 at 13:35

answered Nov 28, 2017 at 12:03

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-11-28 12:05:37Z

3

I would do this using group by and having:

select pv.product_id
from product_values pv join
     properties p
     on pv.property_id = p.id
where (p.name, v.value) in ( ('size', 'big'), ('color', 'red') )
group by pv.product_id
having count(distinct p.name) = 2;

answered Nov 28, 2017 at 12:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

M Khalid Junaid · Accepted Answer · 2017-11-28 12:04:17Z

2

Another approach using sum to filter multiple attributes for an entity

SELECT  
  `p`.* 
FROM
  `products` p
  INNER JOIN `product_values` v
    ON `v`.`product_id` = `p`.`id` 
  INNER JOIN `properties`  pr
    ON `pr`.`id` = `v`.`property_id` 
GROUP BY p.id
HAVING SUM (pr.name = 'size' AND v.value = 'big') 
  AND SUM(pr.name = 'color' AND v.value = 'red')

answered Nov 28, 2017 at 12:04

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Collectives™ on Stack Overflow

SQL query on join table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related