チェックボックスの値を別の累積テーブルにした場合の検索用SQL

Question

[親テーブル]
create table example(
number int unsigned auto_increment not null primary key,
：
);

[親テーブルに紐づくチェックボックス用テーブル]
create table example_checkbox(
example_number int unsigned not null,
：
);

numberとexample_numberで繋がっているのですが、チェックボックス用テーブルには、
チェックボックスにチェックされたIDが累積して格納されております。
本来ならばexampleテーブルに正規化して持たせたかったのですが、チェックボックス
の数は可変するため、別テーブルにしております。

exampleテーブルの一覧を出す際に、example_checkboxでチェックされているか否かの
条件を入れたいのですが、exampleとexample_checkboxは1対多であるため、joinを使うと
example_checkboxの数分結果が返ってきてしまうものと想定しておりますが、何かうまい
やり方はないものでしょうか。

アドバイスいただければ幸いです。

Answer 1

本来ならばexampleテーブルに正規化して持たせたかったのですが、チェックボックス

いや、むしろ今が正規化されていて、もし example テーブルに「check1, check2, check3, ...」などともたせるとむしろ正規化されていないと思います。

exampleテーブルの一覧を出す際に、example_checkboxでチェックされているか否かの
条件を入れたいのですが、exampleとexample_checkboxは1対多であるため、joinを使うと
example_checkboxの数分結果が返ってきてしまうものと想定しておりますが、

example_checkbox が下記のようなテーブルなのだとして・・・（example_check は選択されていたチェックボックスの値）

create table example_checkbox(
  example_number int unsigned not null,
  example_check int  unsigned not null,
  primary key (example_number, example_check)
);

以下のように結合する分には重複は現れないと思いますけど？

select E.*
from example E
inner join example_checkbox C
  on C.example_number = E.number
where
  C.example_check = 3

example_checkbox テーブルは example_number と example_check で一意になるはずなので、example_check = 3 のように example_check を特定すれば、その詳細度は example テーブルと同じ（example_number ごと）だと思います。

（もし、複数の値で OR 検索、であれば話は変わりますが）

---

相関サブクエリで exists したり、

select E.*
from example E
where
  exists (
    select * from example_checkbox C
    where C.example_number = E.number
      and C.example_check in (2, 3, 4)
  )

サブクエリを in してみたり、

select E.*
from example E
where
  E.number in (
    select example_number from example_checkbox
    where example_check in (2, 3, 4)
  )

group by なサブクエリを join したり、

select E.*
from example E
inner join (
    select example_number from example_checkbox
    where example_check in (2, 3, 4)
    group by example_number
) C
on C.example_number = E.number

---

いずれも試してはいないので、上手くできなかったらごめんなさい。

サブクエリを使わずに group by や distinct で重複行を排除するという方法も考えられると思います。

Comments

[grnfnt]

大事なことを書いておらず申し訳ありません。
最後のところでおっしゃる通りで、検索はORが入ります。
つまりは、example_check in (2,3,4)のようになるので複数返ってきて
しまうのが問題とみているのですが、何か良い方法ございますでしょうか。

とてもわかりやすく解説いただきありがとうございます。

[ngyuki]

追記しました

[grnfnt]

素晴らしいの一言です。
実は返答をいただくまでdistinctを使用して試していたのですが、これでも同じ結果が返ってきてうまくいきました。
相関サブクエリは初めて知ったので、これから使っていこうかと思います。

本当に申し分のない素晴らしいご返答をいただき、ありがとうございました。
いただいた内容はドキュメントに入れておきたいと思います。
この場をお借りし御礼申し上げます。