MySQLの「1対多」で、「多」の一つを渡して「1対多の全てに該当するデータ」を取得する書き方

Question

下記関係のデータがありますす
・「a 4」 … 「tag 東京」「tag 大阪」
・「a 5」 … 「tag 大阪」「tag 福岡」

3テーブル
・aテーブル … 1　 　　
・tagテーブル … 多　
・a_tagテーブル … 中間　

---

「a 4」 から、「a 4」「tag 東京」「tag 大阪」を取得する場合は、下記のようなSQLを実行しているのですが、

SELECT
  a.id
  GROUP_CONCAT(DISTINCT t.tag_id),
  GROUP_CONCAT(DISTINCT t.tag)
FROM a
LEFT JOIN a_tag at ON (a.id = at.a_id)
LEFT JOIN tag t ON (t.tag_id = at.tag_id)
WHERE a.id = 4
GROUP BY a.id

「tag 大阪」から、「a4」「tag 東京」「tag 大阪」と、「a 5」「tag 大阪」「tag 福岡」を取得する場合は、どのようなSQLを書けば良いですか？
「1対多」で、「多」の一つを渡して「1対多の全てに該当するデータ」を「GROUP BY a.id」で取得する方法を知りたいです。

---

試してみたこと
WHERE at.tag_id = 大阪 を追加してみたのですが、タグが1つしか取得できませんでした。
※aのid に対してtag が複数存在する場合でも、「tag 大阪」しか取得されない

1対多の場合、WHERE条件はFROM句に対してしか使用不可ですか？

Comments

[maisumakun]

取得条件がわかりません（なぜ「a 5」「tag 大阪」「tag 福岡」が取れるのでしょうか）。

[re97]

取得条件を修正しました。

Answer 1

aテーブルとtagテーブルは、「多：多」です。
そこに「１：多」の関係を持ち込んでいるのが、a_tagテーブル という中間テーブルです。

こういった、中間テーブルを介した情報取得の場合に駆動表とするのは中間テーブルです。

質問のSQLの情報のみなら、a_tagテーブル とtagテーブルのみで取得できます。

SELECT  at.a_id
      , GROUP_CONCAT(DISTINCT t.tag_id)
      , GROUP_CONCAT(DISTINCT t.tag)
FROM  a_tag at 
      inner join tag t 
      ON t.tag_id = at.tag_id
WHERE at.a_id = 4
GROUP BY at.a_id

ただ、データ上GROUP_CONCAT()でDistinctが必要な状況ならDistinctが不要になるようにしておいた方が良いと思われます。

質問にある、「tag='大阪'」を含むデータの抽出は以下です。

SELECT  at.a_id
      , GROUP_CONCAT(DISTINCT t.tag_id)
      , GROUP_CONCAT(DISTINCT t.tag)
FROM  a_tag at 
      inner join tag t 
      ON t.tag_id = at.tag_id
WHERE at.a_id in (select a_id from a_tag where tag = '大阪')
GROUP BY at.a_id

または

SELECT  at.a_id
      , GROUP_CONCAT(DISTINCT t.tag_id)
      , GROUP_CONCAT(DISTINCT t.tag)
FROM  a_tag at 
      inner join tag t 
      ON t.tag_id = at.tag_id
WHERE exists (select 1 from a_tag where tag = '大阪' and a_id = at.a_id)
GROUP BY at.a_id

Comments

[re97]

回答ありがとうございます。

タグを条件指定してSELECTしているのに「FROM a」としていた点が間違っていることに気が付きました。
また、タグが含まれているデータを取得したいのに「WHERE a.id = 4」としている条件指定自体も間違っていることにも気が付きました。
「inner join」についてはこれまで一度も使用したことがなくどういう時に使用するのか理解していないため、これから調べてみたいと思います。

最初のSQL「WHERE at.a_id in (select a_id from a_tag where tag = '大阪')」は「大阪タグのidが含まれいれば」という条件指定だと思うので、何となく分かるのですが、

次のSQL「WHERE exists (select 1 from a_tag where tag = '大阪' and a_id = at.a_id)」も、上記と同義ですか？「大阪タグのid」が存在していれば、という感じですか？

[sazi]

両方とも同じ結果になりますが、SQLの動作が異なります。
後者は相関副問合せとよばれ、FROM句で指定している集合の1件毎に問い合わせが行われます。
existsは1件でも条件に合致していればそこで副問合せは終了する動作です。
データの分布に応じてコストが違ってくるので、性能面で使い分けを行います。

[re97]

再度の回答ありがとうございました。

＞両方とも同じ結果になりますが、SQLの動作が異なります
・なるほど、この説明は分かりやすかったです

＞データの分布に応じてコストが違ってくるので、性能面で使い分けを行います
・ INとEXISTSについて検索してみたら、比較ページが色々見つかりました
・大変参考になりました

Answer 2

WHERE at.tag_id = 東京 を追加してみたのですが、タグが1つしか取得できませんでした。

※aのid に対してtag が複数存在する場合でも、「tag 東京」しか取得されない

どんなデータがあるのか、テーブルの定義をCREATE TABLE, PRIMARY KEYの情報やテーブル中のデータも INSERTで、現行のSELECT結果と希望する結果もMarkdownの [コード] で質問にできた方が適切なコメントが付き易いです。

ネーミングのセンスの問題だけど、テーブル名の a, a_tag, tag ってもう少しテーブルの内容を想像できるものに変えられることができた方が良いです。