SQL Multiple values in one column

Question

I have two tables, the first is the products table, and it has 2 columns: id and product_name. The second table is the filters table, and it has 2 columns: filter_id, product_id.

For example i have this in the products table:

id | product_name
1  | test product

and this in the filters table:

filter_id | product_id
1         | 1
2         | 1
3         | 1

As you can see, the product with id '1' has 3 filters.

My goal is to get the products by filters. For example, i need every product where the product has the 2 and 3 filter_id. I tried to use something like that:

SELECT * FROM products p LEFT JOIN filters f ON (p.id = f.product_id) WHERE (filter_id = '2' AND filter_id = '3')

I can't do that because the filter_id can't be equal to 2 and 3 at the same time. The main problem is that i should use only one query to get the products.

juergen d juergen d · Accepted Answer · 2014-08-25T10:41:01

Group by the product and select only those having both filter_ids

SELECT p.id, p.product_name
FROM products p 
JOIN filters f ON p.id = f.product_id
WHERE f.filter_id in (2,3)
group by p.id, p.product_name
having count(distinct f.filter_id) = 2

SQL Multiple values in one column

1 Answers