外部キーを利用したタグのAND検索の方法


MySQLを使っています。条件「タグ"青"かつタグ"大"と紐付けられたポスト」の検索方法がわかりません。

以下のようなテーブルを作成しました。

[code]

    mysql> CREATE TABLE IF NOT EXISTS post (
      id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    );
    
    mysql> CREATE TABLE IF NOT EXISTS tag (
      id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255)
    );
    
    mysql> CREATE TABLE IF NOT EXISTS tagpost (
      id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
      post_id INTEGER NOT NULL,
      tag_id INTEGER NOT NULL,
      FOREIGN KEY (post_id) REFERENCES post(id),
      FOREIGN KEY (tag_id) REFERENCES tag(id)
    );
    
[/code]

データは以下のように挿入しました。

[code]

    mysql> SELECT * FROM post;
    +----+
    | id |
    +----+
    |  1 |
    |  2 |
    +----+
    2 rows in set (0.00 sec)
    
    mysql> SELECT * FROM tag;
    +----+------+
    | id | name |
    +----+------+
    |  8 | 青   |
    |  9 | 大   |
    +----+------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT * FROM tagpost;
    +----+---------+--------+
    | id | post_id | tag_id |
    +----+---------+--------+
    |  1 |       1 |      8 |
    |  2 |       1 |      9 |
    |  3 |       2 |      8 |
    +----+---------+--------+
    3 rows in set (0.00 sec)
    
[/code]

この時、「タグ"青"またはタグ"大"と紐付けられたポスト」を検索する場合は以下のSQLで出来たのですが、

[code]

    mysql> SELECT * FROM post
      INNER JOIN tagpost ON tagpost.post_id = post.id
      INNER JOIN tag ON tagpost.tag_id = tag.id
      WHERE tag.name = '青' OR tag.name = '大'
      ;
    
[/code]

別の条件で、「タグ"青"かつタグ"大"と紐付けられたポスト」を検索する方法がわかりません。  
わかる人いたら教えてください。よろしくお願いいたします。




少し分かりにくくはなりますが、拡張性を考えるのであれば、こちらが良いかもしれません。

[code]

    SELECT * FROM post p
    WHERE 2 = (SELECT COUNT(*) from tagpost
        INNER JOIN tag ON tagpost.tag_id = tag.id
        WHERE p.id = tagpost.post_id AND tag.name IN ('青', '大'))
    
[/code]

tag.nameが'青'か'大'でのタグの件数を集計して、その件数が2件(検索するタグの種類数)に一致するか、という条件です。  
IN句の中と2という件数を条件に応じて変更するだけで済みます。  
たぶんパフォーマンスも良いのではないかと。  
検証していないのでご参考程度にどうぞ。




以下のようなSQLで行けると思います。

[code]

    SELECT * FROM post AS p
    INNER JOIN
    (SELECT tagpost.post_id FROM tagpost
      INNER JOIN tag ON tagpost.tag_id = tag.id
      WHERE tag.name = '青') blue
      ON blue.post_id = p.id
    INNER JOIN
    (SELECT tagpost.post_id FROM tagpost
      INNER JOIN tag ON tagpost.tag_id = tag.id
      WHERE tag.name = '大') big
      ON big.post_id = p.id
    
[/code]

考え方は、「青」のタグを持つPOSTの一覧をサブクエリで作成し、  
同様に「大」のタグを持つPOSTの一覧をサブクエリで作成します。  
その両方に内部結合できるもの、すなわち両方の一覧に存在しているものを、POSTより抽出します。

