CODEWITHSUNDEEP
sqlpostgresql
Sergey Voronov
Sergey Voronov

Reputation: 3

How to filter rows in one table with values from joined table

products
| id |
------
|  1 |
|  2 |

attributes
| product_id |           value |
--------------------------------
|          1 | Needed value #1 |
|          1 | Needed value #2 |
|          2 | Needed value #1 |

I need to select a product that has both Needed value #1 and Needed value #2. Can't get it with join and where clause. It selects every products or no product at all. Are there any method that more elegant than use multiple inner joins?

SELECT * FROM products
INNER JOIN attributes ON products.id = attributes.product_id
WHERE attributes.value IN ('Needed value #1', 'Needed value #2')

SELECT * FROM products
INNER JOIN attributes ON products.id = attributes.product_id
WHERE attributes.value = 'Needed value #1'
OR /* AND */ attributes.value = 'Needed value #2'

Upvotes: 0

Views: 37

Answers (2)

forpas
forpas

Reputation: 164069

You can do it by grouping by product_id the attributes table:

SELECT * FROM products
WHERE id IN (
  SELECT product_id FROM attributes
  WHERE value IN ('Needed value #1', 'Needed value #2')
  GROUP BY product_id
  HAVING COUNT(DISTINCT value) = 2
)

The condition COUNT(DISTINCT value) = 2 in the HAVING clause makes sure that both attributes exist for this product.

Upvotes: 0

cn0047
cn0047

Reputation: 17061

You cat do something like this:

SELECT * FROM products
INNER JOIN attributes AS a1 ON
    products.id = a1.product_id AND a1.value = 'Needed value #1'
INNER JOIN attributes AS a2 ON
    products.id = a2.product_id AND a2.value = 'Needed value #2'

Upvotes: 1

Related Questions