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 JOIN
s 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.
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:
in
clause (the number of wanted tags is variable, which affects the query string)having
clauseNote 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