How do I efficiently do the intersection of joins in SQL? -

i have 3 tables, books, tags, , taggings (books-xref-tags):

books id | title |      author       1 | blink | malcolm gladwell  2 |  1984 |    george orwell  taggings book_id | tag_id       1 |      1       1 |      2       2 |      1       2 |      3  tags id | name  1 | interesting  2 |  nonfiction  3 |     fiction 

i'd search books tagged both "interesting" and "fiction." best i've come is

select books.* books, taggings, tags  taggings.book_id = books.id    , taggings.tag_id  = tag.id    , tag.name = "interesting" intersect select books.* books, taggings, tags  taggings.book_id = books.id    , taggings.tag_id  = tag.id    , tag.name = "fiction" 

that seems work, i'm not sure how scale, either in rows or number of tags. is, happens add hundreds of books, hundreds of tags, , thousands of taggings? happens search becomes "'interesting' and 'fiction' and 'aquatic' and 'stonemasonry'"?

i have alternative approach in mind if there's no better way of doing query directly in sql:

1. select books first tag, along of books' tags
2. remove list don't have of tags queried

if want keep option of using more 2 tags, this answer similar interesting you.

it uses mysql syntax (not sure use), quite simple , should able use other databases.

this (using mysql syntax):

select books.id, books.title, books.author books inner join taggings on ( taggings.book_id = books.book_id ) inner join tags on ( tags.tag_id = taggings.tag_id ) tags.name in ( @tag1, @tag2, @tag3 ) group books.id, books.title, books.author having count(*) = @number_of_tags 

from other post:

if have 3 tags in example number_of_tags have 3, , join result in 3 rows per id matches.

you can either create query dynamically, or define with, say, 10 tags , initialize them value won't occur in tags.