is there an equivalent of the "IN" function for adding "AND"s to queries

Question

i have the following tables in sql server:

photoalbumsTable:
album_ID
album_caption

albumtagmap
id
album_id
tag_id

albumtags
id
tag_name

i have the following query which will show me where t.tag_name is in "Trips" OR 'Beach":

SELECT b.* FROM albumtagmap bt, photoalbums b, albumtags t
                     WHERE bt.tag_id = t.id
                     AND (t.tag_name IN ('Trips', 'Beach'))
                     AND b.album_id = bt.album_id
                     GROUP BY b.album_id, b.album_caption, b.active, b.description, b.album_name, b.album_date, b.highlight_picture
                     ORDER BY b.album_date desc

what is the equivalent query if i want to show where t.tag_name is in "Trips" AND 'Beach' meaning where there are entries in the albumtagmap table for this particular photoalbum for each tag.

Answer 1

Sounds like something you might want to use a JOIN of two subqueries for (one query for each tag, then inner join on album_id). You could also potentially use just an AND of two EXISTS subqueries.

Answer 2

( SELECT b.* FROM albumtagmap bt, photoalbums b, albumtags t
WHERE bt.tag_id = t.id
AND t.tag_name='Trips'
AND b.album_id = bt.album_id )

INTERSECT

( SELECT b.* FROM albumtagmap bt, photoalbums b, albumtags t
WHERE bt.tag_id = t.id
AND t.tag_name = 'Beach'
AND b.album_id = bt.album_id )

ORDER BY  album_date desc