CODEWITHSUNDEEP
mysqlsqlselectinner-join
Pioz
Pioz

Reputation: 6321

SQL query on join table

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.

Upvotes: 0

Views: 396

Answers (3)

juergen d
juergen d

Reputation: 204756

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

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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;

Upvotes: 3

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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')

Upvotes: 2

Related Questions