Finding entries with all relations in a relational database table

Question

I am making a relational database using tags. The database has three tables:

• object

• match

• tag

where match is a simple relation between an object and a tag (i.e. each entry consists of a primary key and two foreign keys). I want to structure a query where I can find all objects with all given tags, but am uncertain how to do it.

For instance, these are the three tables:

• Object

1. Death becomes her
2. Billy Madison

• Tag

1. Comedy
2. Horror

• Match

1. 1 | 1
2. 1 | 2
3. 2 | 1

Given that someone wants a horror-comedy, how do I structure the query to find only the objects with all matches? I realize this is elementary but I genuinely haven't found any answers. If the whole schema is off naturally feel free to point that out.

For the record I'm using Python, SQLAlchemy, and SQLite. Currently I've made a list of all tag IDs to find in Match.

Edit: For any future reference, I used astentx' solution with a slight modification to the query in order to access data from object right away:

select object.Length, object.title
    from object
    join match
    on object.id = match.object
    join tag
    on match.tag = tag.id
    join filter_tags
    on tag.name = filter_tags.word

Answer 1

You can pass all your tags as array and use Carray() function or as comma-separated string and transform it to table in this way, for example. Then for AND condition select rows that have exactly the same tags as you've expected:

select relation.obj_id
from relation
  join tags
    on relation.tag_id = tags.id
  join <generated table>
    on tagsvalue = <generated table>.value
group by relation.obj_id
having count(1) = (select count(1) from <generated table>)

Fiddle here.