CODEWITHSUNDEEP
mysqlsql
Peter Valek
Peter Valek

Reputation: 87

SQL Multiple filters in same columns for filtering product

I am trying to filter products from my database. I want to select products, they meet my conditions.

I have a table with filters and assigned products

filter value product_id
Memory 2048 1
Brand Samsung 1
Memory 2048 2
Brand Xiaomi 2

Now I need to filter Product with (filter = 'Memory' AND value = '2048') AND (filter = 'Brand' AND value = 'Samsung')

This is always returning empty rows.

I am using Mariadb 10.5

Upvotes: 0

Views: 2373

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use aggregation, with a having clause being sure that all filters pass:

select product_id
from t
where (filter = 'Memory' AND value = '2048') or
      (filter = 'Brand' AND value = 'Samsung')
group by product_id
having count(*) = 2;

The count(*) = 2 is ensuring that both conditions are met (well, assuming you don't have duplicate rows in the table, which seems like a reasonable assumption).

Upvotes: 1

Related Questions