38907


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]

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


CC BY-SA 3.0
2017-10-20T06:58:18.353
38899
2017-10-20T09:08:54.463
2017-10-20T07:11:30.600
25852
25852
question
mysql sql
外部キーを利用したタグのAND検索の方法


以下のような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より抽出します。


CC BY-SA 3.0
2017-10-20T07:59:59.343
38901
2017-10-20T08:35:36.987
2017-10-20T08:35:36.987
25852
17014
38899
answer


こういう考え方もありますね。

[code]

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

nameが"青"のtagが存在し、かつtag.nameが"大"のものが存在するpostという条件を取っています。  
パフォーマンスや条件が増えたときにどうするかは別途考慮する必要があるかもしれませんが、分かりやすくはあります。


CC BY-SA 3.0
2017-10-20T08:35:36.940
38905
2017-10-20T08:35:36.940
23348
38899
answer


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

[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という件数を条件に応じて変更するだけで済みます。  
たぶんパフォーマンスも良いのではないかと。  
検証していないのでご参考程度にどうぞ。


CC BY-SA 3.0
2017-10-20T09:08:54.463
38907
2017-10-20T09:08:54.463
23348
38899
answer