CODEWITHSUNDEEP
mysqlsql
bnshr
bnshr

Reputation: 11

SQL Filter products from one table basing on attributes from second one

So I have table with products

id | product_name

 1 | Product 1
 2 | Product 2
 3 | Product 3

... table with atributtes:

id | attribute

 1 | big
 2 | orange
 3 | expensive

and table with products and their attributes

id  | product_id | attribute_id

 1  |     1      |  1
 2  |     1      |  2
 3  |     2      |  3
 4  |     3      |  2

and what I want is to filter big, orange products.. in this case: Product 1

Something like:

SELECT product_name 
  FROM products as a 
  JOIN products_attributes as b ON a.id=b.product_id 
 WHERE b.attribute_id = 1 OR b.attribute_id=2

will not work as it returns Product 3 as well..

This doesn't work too, of course:

SELECT product_name 
  FROM products as a 
  JOIN products_attributes as b ON a.id=b.product_id
 WHERE b.attribute_id = 1 AND b.attribute_id=2

Please help :)

Upvotes: 1

Views: 460

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You need to add atributtes table into your SELECT statement and

filter by attribute IN ( 'big','orange' )

GROUPing with HAVING clause should be added to satisfy the both conditions at the same time

SELECT p.product_name
  FROM products as p
  JOIN products_attributes as pa
    ON p.id = pa.product_id
  JOIN attributes a
    ON a.id = pa.attribute_id  
 WHERE a.attribute IN ( 'big','orange' )
 GROUP BY p.product_name
HAVING COUNT(DISTINCT a.attribute) = 2

using IN rather than OR operator is more straightforward to use .

Demo

Upvotes: 2

El_Vanja
El_Vanja

Reputation: 3983

In order to find products that have multiple attributes, first we need to join the attributes table with itself:

SELECT pa1.product_id FROM products_attributes as pa1
JOIN products_attributes as pa2 ON pa1.product_id = pa2.product_id
    AND pa1.attribute_id = 1 AND pa2.attribute_id = 2

This will output 1, which is the correct id. Note that it doesn't matter which of the two tables you will select the product_id from, since you're joining it with itself and both will contain it.

Now all we need is the name, so we're going to join the products table as well and change our selection:

SELECT p.product_name FROM products p
JOIN products_attributes as pa1 ON pa1.product_id = p.id
JOIN products_attributes as pa2 ON pa1.product_id = pa2.product_id
    AND pa1.attribute_id = 1 AND pa2.attribute_id = 2

This should output "Product1".

Upvotes: 0

Related Questions