SQL filter on unknown number of columns

Question

I have table containing products, a table containing tags for the products where each product can have several tags, eg

products

product_id|name|…
1         |book
2         |game
3         |desk
…

,

tags

product_id|tag
3         |used
3         |physical

Is there a “nice” way using only SQL to query for all products which have all given tags where the tags are in an array only known at compile time?

The only way I can think of is programmatically generating a query with multiple JOINs on tags, renaming to tag1, tag2 and so on and then filtering in the WHERE clause with tag1 = tags_array[1] AND tag2 = tags_array[2] and so on.

I am using SQLITE, if this is relevant.

Answer 1

For a given list of tags, you can use relational division. Say we want the ids of all products that have both tags 'used' and 'physical'

select product_id
from tags
where tag in ('used', 'physical')     -- either one tag or the other
group by product_id                     
having count(*) = 2                     -- both are available for this product

If you are going to parameterized this query, you just need to:

• programmatically generate the list of products in the in clause (the number of wanted tags is variable, which affects the query string)
• pass the count of parameter to the having clause

Note that SQLite does not support arrays out of the box.

If you want the product name as well, we can join with products:

select p.id, p.name
from products p
inner join tags t on t.product_id = p.product_id
where t.tag in ('used', 'physical')
group by p.id, p.name
having count(*) = 2

Answer 2

You can use a combination of GROUP BY, HAVING, and a subquery:

SELECT p.product_id, p.name
FROM products p
INNER JOIN tags t ON p.product_id = t.product_id
WHERE t.tag IN ('tag1', 'tag2', 'tag3')
GROUP BY p.product_id, p.name
HAVING COUNT(DISTINCT t.tag) = 3; 

Answer 3

You can filter on an array of tags to easily count its length, which can then be used in the having clause.

select t.product_id, max(p.name)
from products p
inner join tags t on t.product_id = p.product_id
inner join (
  SELECT value as tag 
  FROM json_each('["used", "physical"]')
) as s on s.tag = t.tag
group by t.product_id
having count(*) = json_array_length('["used", "physical"]');

Demo here