紐付いている３つのテーブルに対しての検索条件の指定がうまくいきません・・。
紐付いている３つのテーブルに対しての検索条件の指定がうまく書けません。
３つのテーブルが紐付いています。
・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
