SQLite: Tags and Products intersection

user_13654336 0
Viewed 66

I'm new to SQLite and searching for a way to create a query to do the following:

I have 3 tables:

goods: List of goods
tags: list of tags
tag_goods: table used to associate a tag to a product

This is the structure for each table:

Goods:

id (INTEGER, autoincrement)
name (TEXT, name of the product)

Tags:

id (INTEGER, autoincrement)
name (TEXT, name of the tag)

Tag_goods:

tag_id (INTEGER)
goods_id (INTEGER)
UNIQUE (tag_id, goods_id)

What I want:

Obtain ONLY ids and names of the products that are tagged with ALL of the possible tags (NB: I need to reference all the tags in the query, not one by one).

(Meaning, for example I only have four tags: "yellow", "round", "eatable", "organic" and only goods "Cheese" and "Melon" match all of them)

With my very limited experience I understand, that I can try something like intersect, but it is impractical when there are too many tags.

sqlite sql database
1 Answers

This query:

select goods_id
from tag_goods
group by goods_id
having count(*) = (select count(*) from tags)

returns all the ids of goods that have all the tags from tags.
You can use it to return the rows from the table goods with the operator IN:

select * from goods
where id in (
  select goods_id
  from tag_goods
  group by goods_id
  having count(*) = (select count(*) from tags)
)
user_104988280
Related