特定のタグをすべて含むアイテムを検索するSQL

全文検索エンジンとかを使わない普通のRDBの従来のテーブル構成の場合。
気づくまで時間がかかったのでメモっておく。ER図もないので分かりにくいけど。。

課題

あるアイテムに複数のタグが関連テーブル経由の外部テーブルとして紐づいていいる。 選択されたタグをすべて含むアイテムのみを抽出したい。どうすればよいか。

対応

1 まず対象となるタグIDを持つタグを得る

select id from tags where name in ('tag1', 'tag2')

2 そのタグIDを持つアイテムタグ関連テーブルを得る

select item_id from items_tags
   where tag_id in ( select id from tags where name in ('tag1', 'tag2') )

3 全てのタグを両方含む必要があるので、item_idでグルーピングしてカウントがタグの数と一致したものみ抽出する

select item_id from items_tags
where tag_id in ( select id from tags where name in ('tag1', 'tag2') )
group by item_id having count(item_id) = 2)

4 最後に得られたitem_idでitemテーブルにクエリーを投げればOK

select * from items
where id in 
 ( select item_id from items_tags
   where tag_id in ( select id from tags where name in ('tag1', 'tag2') )
   group by item_id having count(item_id) = 2)