紐付いている３つのテーブルに対しての検索条件の指定がうまくいきません・・。

質問

紐付いている３つのテーブルに対しての検索条件の指定がうまく書けません。

３つのテーブルが紐付いています。
・main_tableのIDと、note_id_tableのID
・note_id_tableのnote_IDと、note_value_tableのnote_ID

main_table
ID, name
-----------
1, aaa
2, bbb
3, ccc
4, ddd
5, eee

note_id_table
ID, note_ID
-----------
1, 1
1, 2
1, 3
1, 4
3, 5
3, 6
4, 7
4, 8
4, 9
5, 10

note_value_table
note_ID, note_value
-----------
1, AAAA1
2, BBBB1
3, CCCC1
4, DDDD1
5, AAAA3
6, EEEE3
7, FFFF4
8, GGGG4
9, HHHH4
10, IIII5

検索の対象にしたいのは、note_valueです。

今まで、「note_valueに検索文字列が含まれている」main_tableのレコードを取得していました。

SELECT main_table.* FROM main_table
LEFT JOIN note_id_table ON main_table.id = note_id_table.id
LEFT JOIN note_value_table ON note_id_table.note_ID = note_value_table.note_ID
WHERE note_value_table.note_value LIKE '%AAA%'

上記クエリだと、note_valueにAAAを含んでいるnote_ID:1,5を持つ、
main_tableのID:1,3のレコード、２件が返ってきます。

これを、検索文字列のNOT検索とAND検索に対応させたいのです。
NOT検索について、

WHERE NOT(note_value_table LIKE '%AAA%')

としてみたのですが、main_tableのID:1,3,4,5のレコードが返ってきました。
期待した動作は、main_tableのID:2,4,5でした。
まず、note_value_tableにデータを持っていないmain_table.id:2はNULLのようで、
この条件では無視されてしまうようです。
また、main_table.id:1,3が返ってきてしまうのは、
main_table.id:1に紐付いているnote_IDの内、2,3,4と
main_table.id:3に紐付いているnote_IDの内、6に条件が引っかかってしまうようなのです。

AND検索ですが、上記クエリの条件を

WHERE note_value_table LIKE '%AAA%' AND note_value_table LIKE '%BBB%'

としたところ、0件となってしまいました。
期待した動作は、note_valueにAAAとBBBを含んでいるnote_ID:1を持つ、
main_tableのID:1のレコード、この１件が返ってくることでした。
どうもこの条件だと、一つのnote_valueにAAAとBBBが含まれていないとHITしないようです。

結局JOINしているので、note_valueの数だけ同じmain_tableが前にくっついてるイメージなんですよね。
main_tableのレコード末端に、紐付いているnote_valueのフィールドを横に繋げるか、
紐付いているnote_valueの文字列を連結したものを一つのフィールドとして解釈するようなことができれば
実現できるのかなぁと思いました。

試行錯誤の末に、パフォーマンスが非常に悪いんですが、下記クエリで機能的には実現できたのですが、
これでは使えないので何かいいアイディアなどあればご教授お願いしたいです。
サブクエリを使いたくないというのがあります。

--AAAの検索
JOINする時に検索してしまうようにしてみました。
また、そのためには、note_id_tableからnote_IDを取得できていないとだめなので、
サブクエリ化してみました。

SELECT main_table.* FROM main_table
LEFT JOIN note_value_table ON note_value_table.note_ID
IN (SELECT note_ID FROM note_id_table WHERE main_table.id = note_id_table.id)
AND note_value_table LIKE '%AAA%'
WHERE note_value_table.note_value IS NOT NULL

--AAAのNOT検索
WHERE条件を逆にするだけにしました。

--AAAとBBBの検索
検索条件１つに対して、JOINを増やしていくようにしました。
JOINが増えるのもそうですが、サブクエリも倍になってしまい、
検索条件を増やせば増やすほどパフォーマンスが落ちてしまいます。。

SELECT main_table.* FROM main_table
LEFT JOIN note_value_table AS note_value_table1 ON note_value_table1.note_ID
IN (SELECT note_ID FROM note_id_table WHERE main_table.id = note_id_table.id)
AND note_value_table1 LIKE '%AAA%'
LEFT JOIN note_value_table AS note_value_table2 ON note_value_table2.note_ID
IN (SELECT note_ID FROM note_id_table WHERE main_table.id = note_id_table.id)
AND note_value_table2 LIKE '%AAA%'
WHERE note_value_table1.note_value IS NOT NULL AND note_value_table2.note_value IS NOT NULL

回答No.2

yambejp · ベストアンサー

#1さんの指摘もありますが、SQLを理解していないのであれば、もうすこし
基本的な論理式の考え方からきちんと学習した方がよいでしょう。

ちなみに'%AAA%'と書いている時点でインデックスが有効ではないので
パフォーマンスは期待できません。

今回の件はサブクエリが使えない前提でテンポラリを使ったやり方を紹介
しておきます。

(1)note_valueに「AAA」が含まれる検索
CREATE TEMPORARY TABLE temp
SELECT DISTINCT note_id_table.ID FROM note_id_table
INNER JOIN note_value_table ON note_id_table.note_ID = note_value_table.note_ID
AND note_value_table.note_value LIKE '%AAA%';
SELECT main_table.* FROM main_table
INNER JOIN temp ON temp.ID=main_table.ID

(2)note_valueに「AAA」が含まれまれない検索
CREATE TEMPORARY TABLE temp
SELECT DISTINCT note_id_table.ID FROM note_id_table
INNER JOIN note_value_table ON note_id_table.note_ID = note_value_table.note_ID
AND note_value_table.note_value LIKE '%AAA%';
SELECT main_table.* FROM main_table
LEFT JOIN temp ON temp.ID=main_table.ID
WHERE temp.ID IS NULL

(3)note_valueに「AAA」および「BBB」が共に含まれまれる検索
結果:1
CREATE TEMPORARY TABLE temp1
SELECT DISTINCT note_id_table.ID FROM note_id_table
INNER JOIN note_value_table ON note_id_table.note_ID = note_value_table.note_ID
AND note_value_table.note_value LIKE '%AAA%';
CREATE TEMPORARY TABLE temp2
SELECT DISTINCT note_id_table.ID FROM note_id_table
INNER JOIN note_value_table ON note_id_table.note_ID = note_value_table.note_ID
AND note_value_table.note_value LIKE '%BBB%';
SELECT main_table.* FROM main_table
LEFT JOIN temp1 ON temp1.ID=main_table.ID
LEFT JOIN temp2 ON temp2.ID=main_table.ID
WHERE not(temp1.ID IS NULL)
AND not(temp2.ID IS NULL)

(4)note_valueに「AAA」、「BBB」の少なくともどちらか一方がふくまれる検索
結果：1,3
(3)の最後の２行を
WHERE not(temp1.ID IS NULL)
OR not(temp2.ID IS NULL)
とする

(5)note_valueに「AAA」「BBB」の多くともどちらか１つしか含まれない検索
結果：2,3,4,5
(3)の最後の２行を
WHERE temp1.ID IS NULL
OR temp2.ID IS NULL
とする

(6)note_valueに「AAA」「BBB」の多くともどちらか１つしか含まれない検索
結果：2,4,5
(3)の最後の２行を
WHERE temp1.ID IS NULL
AND temp2.ID IS NULL
とする

お礼

回答ありがとうございます。
質問が長文になってしまったにもかかわらず、
質問の意図を理解して更にクエリの例まであげてもらい感謝しております。

どうも私はクエリ一つで実現させようとそこにこだわりすぎてたようです。。
テンポラリテーブルを利用することは頭の片隅にもありませんでした。

条件別に例を示して頂いて大変分かりやすく本当に感謝してます。
教えていただいたものを参考に動作させてみましたが、
対象レコードが多い場合はそれでも重くなってしまったので、
テンポラリテーブル作成後、テンポラリテーブルにインデックスを貼ったところ
パフォーマンス向上しました。

本当にありがとうございました。

回答No.1

chukenkenkou

RDBMSは、ここのカテゴリ通りMySQLですか？
MySQLはバージョンにより、多くの機能追加や一部の仕様変更があるので、バージョンを明記するようにしてください。

質問内容を細かく見ていないのですが、ちょっと眺めた印象では、ONで指定する条件（結合条件）と、WHEREで指定する条件（制限条件）の違いが分かっていないようですね。
また、ANDとORの使い方も、理解されていないようです。

WHEREで指定する条件は、最終的な検索結果を絞り込む動きをします。つまり、WHEREで指定した条件に該当しないデータは、検索結果として得られません。
（内部的には、INNER JOINの動きになりますが、この辺は理解は難しいかな。。。アクセス計画を意識するレベルになったら、この辺も勉強してみてください）

一方、ONで指定する条件は、合致するデータはその行の各列値が返され、それ以外のデータはNULLが返ってきます。つまり、条件に該当しないデータも、返ってくる訳です。

お礼

アドバイスありがとうございます。
使用しているmysqlのバージョンは、5.0.24です。

ご指摘通り、結合条件と制限条件の違いについて理解の欠しい部分があったかもしれません。
ただ、どうしてもクエリ一つで実現することにこだわりすぎて、
結合条件と制限条件がごちゃまぜになってしまったようです。。

質問が長文になってしまって、目を通して頂いたこと感謝致します。
ありがとうございました。