Suppose I have a "tags" table with two columns: tagid and contentid. Each row represents a tag assigned to a piece of content. I want a query that will give me the contentid of every piece of content which is tagged with tagids 334, 338, and 342.
The "easy" way to do this would be (pseudocode):
select contentid from tags where tagid = 334 and contentid in (
select contentid from tags where tagid = 338 and contentid in (
select contentid from tags where tagid = 342
)
)
However, my gut tells me that there's a better, faster, more extensible way to do this. For example, what if I needed to find the intersection of 12 tags? This could quickly get horrendous. Any ideas?
EDIT: Turns out that this is also covered in this excellent blog post.