SQLite – Select posts based on tags

I have two tables in my database: posts, which contains post id and other informations, and tags. In tags, there is a column post_id and a column tag. My idea is to store each tag in a separate row. For instance, a post with id 1 and tags foo, bar and eggs would be stored in tags as

post_id | tag
---------------
1       | foo
1       | bar
1       | eggs

Which is the simplest way to get the id of a post with these three tags from the database? Thank you in advance!

Answer

You need aggregation.

If you want the post_ids that have these 3 tags and no others:

SELECT post_id
FROM tags
GROUP BY post_id
HAVING SUM(tag IN ('foo', 'bar', 'eggs')) = 3 -- or COUNT(*) = 3
   AND SUM(tag NOT IN ('foo', 'bar', 'eggs')) = 0

If you want the post_ids that have these 3 tags and maybe other tags too then it is simpler:

SELECT post_id
FROM tags
WHERE tag IN ('foo', 'bar', 'eggs')
GROUP BY post_id
HAVING COUNT(*) = 3

I assume that each combination of post_id and tag is unique.