質問: 閲覧ありがとうございます! 以前は | id | メニュー | Water | POCARI SWEAT | | --- | --- | --- | --- | | 1 | メニュー1 | 1 | 1 | | 2 | メニュー2 | 2 | 2 | | 3 | メニュー3 | 1 | 1 | のようなテーブルを作成していたのですが、これだとDBの保守には相応しく無いと本に書いてあったので、 | id | メニュー | | --- | --- | | 1 | メニュー1 | | 2 | メニュー2 | | id | 外部キー | 外部キー | 個数 | | --- | --- | --- | --- | | 1 | メニュー1 | ドリンク1 | 1 | | 2 | メニュー1 | ドリンク1 | 1 | | id | ドリンク | | --- | --- | | 1 | Water | | 2 | POCARI SWEAT | といった、中間テーブルを設けて、勉強しています。 問題点 --- 以前のテーブル構造だと`where(water=1, POCARI SWEAT=1)`みたいな書き方をすれば、条件を複数設定することが出来ました。 しかし、中間テーブルの場合だとjoinを使っても | id | メニュー | 個数 | ドリンク | | --- | --- | --- | --- | | | メニュー1 | 1 | Water | | | メニュー1 | 1 | POCARI SWEAT | | | メニュー2 | 2 | Water | というテーブルが作成されてしまうため、以前の様なSQLで検索出来ません。 `where(water=1)`と`where(POCARI SWEAT=1)`の積集合で求めることは可能なのですが、コードも増え扱いづらいので、いっその事テーブルを一時的に変更する事が可能ならばどうだろうかと思いました。 テーブル構造は中間テーブルを使っているが、検索時には以前のテーブル構造に変換する事は可能ですか? よろしくお願いいたします!
回答: 元のテーブルが何を意味するのかが質問からは把握できませんでした。 売上なのか、セットメニューなのかといったところかと推測しています。 そのため、どのようなテーブル構造がよいのかという判断はできていません。 また、分割後のテーブルは、元のテーブルのデータ構造を正規化したものとなっていません。 つまり同じものとして扱うことはできないので、そのままでは元のテーブルを再現することはできません。 列名を動的に与える方法をすぐには思いつきませんでしたのでドリンク名は固定としていますが、下記のようにすれば元のテーブルの再現は可能かと思います。 ``` -- CREATE TABLE menus (id INT AUTO_INCREMENT PRIMARY KEY, menu VARCHAR(16)); -- INSERT INTO menus (menu) VALUES ('メニュー1'), ('メニュー2'), ('メニュー3'); -- CREATE TABLE drinks (id INT AUTO_INCREMENT PRIMARY KEY, drink VARCHAR(16)); -- INSERT INTO drinks (drink) VALUES ('Water'), ('POCARI SWEAT'); -- CREATE TABLE menudrink (id INT AUTO_INCREMENT PRIMARY KEY, menu_id INT, drink_id INT, number INT); -- INSERT INTO menudrink (menu_id, drink_id, number) VALUES (1, 1, 1), (1, 2, 1), (2, 1, 2), (2, 2, 2), (3, 1, 1), (3, 2, 1); SELECT m.id, m.menu, md1.number `Water`, md2.number `POCARI SWEAT` FROM menus m INNER JOIN menudrink md1 ON m.id = md1.menu_id AND md1.drink_id = 1 INNER JOIN menudrink md2 ON m.id = md2.menu_id AND md2.drink_id = 2 ORDER BY 1; +----+---------------+-------+--------------+ | id | menu | Water | POCARI SWEAT | +----+---------------+-------+--------------+ | 1 | メニュー1 | 1 | 1 | | 2 | メニュー2 | 2 | 2 | | 3 | メニュー3 | 1 | 1 | +----+---------------+-------+--------------+ SELECT * FROM (SELECT m.id, m.menu, md1.number 'Water', md2.number 'POCARI SWEAT' FROM menus m INNER JOIN menudrink md1 ON m.id = md1.menu_id AND md1.drink_id = 1 INNER JOIN menudrink md2 ON m.id = md2.menu_id AND md2.drink_id = 2) tmp WHERE `Water` = 1 AND `POCARI SWEAT` = 1 ORDER BY 1; +----+---------------+-------+--------------+ | id | menu | Water | POCARI SWEAT | +----+---------------+-------+--------------+ | 1 | メニュー1 | 1 | 1 | | 3 | メニュー3 | 1 | 1 | +----+---------------+-------+--------------+ ``` ただし、この構造だとドリンクの数だけ列が増えていくことになります。 Water、POCARI SWEATの個数がそれぞれ1のメニューを取得したいということであれば、下記のようになるのではないかと考えられます。 ``` SELECT m.id, m.menu FROM menus m WHERE EXISTS (SELECT md.id FROM menudrink md WHERE m.id = md.menu_id AND drink_id = 1 AND md.number = 1) AND EXISTS (SELECT md.id FROM menudrink md WHERE m.id = md.menu_id AND drink_id = 2 AND md.number = 1) ORDER BY 1; +----+---------------+ | id | menu | +----+---------------+ | 1 | メニュー1 | | 3 | メニュー3 | +----+---------------+ ``` どちらの場合も、プログラム側で動的にSQLを組み立てることになりますので、扱いやすい方を選ばれるとよいかと思います。 追記: 今回のデータを考える場合、出発点となる非正規テーブルは下記のような形となるのかと思います。 * セットメニュー | id | メニュー名 | ドリンク1 | 個数 | ドリンク2 | 個数 | | --- | --- | --- | --- | --- | --- | | 1 | メニュー1 | Water | 1 | POCARI SWEAT | 1 | | 2 | メニュー2 | Water | 2 | POCARI SWEAT | 2 | | 3 | メニュー3 | Water | 1 | POCARI SWEAT | 1 | このテーブルをデータの重複や繰り返しをなくす形で分割した場合、質問に記載の3テーブルへの分割は、正規化後の構造として問題ないものと思われます。 正規化の妥当性を検証するためには、分割後のテーブルから上記のテーブルが作成できることを確認されるとよいかと思います。 前述しているSQLにドリンク名が表示されるように組み立てると、再現できるかと思います。 > > 一番最後のSQLが理解できないです。なぜこのSQLで積集合みたいなことが出来るのですか? > > 仮にメニュ−2のwaterの個数が1だった場合、一つ目のwhereはメニュー123が該当してしまうため、結果としてメニュー123がピックアップされると思うのですが。。。。。 > > > メニュー2のWaterの個数が1、POCARI SWEATの個数が2の場合を考えます。 この場合、下記のSQLの結果としてメニュー2も含まれるようになります。 > > SELECT md.id FROM menudrink md WHERE m.id = md.menu_id AND drink_id = 1 AND md.number = 1 > > > ただし、下記のSQLについては、POCARI SWEATの個数が2であるためにメニュー2のidは結果に含まれません。 > > SELECT md.id FROM menudrink md WHERE m.id = md.menu_id AND drink_id = 2 AND md.number = 1 > > > EXISTSは結果が1行でも存在すれば真(true)、0件であれば偽(false)となる演算で、上記2つのSELECT文をWHERE句のAND条件で使用しています。 AND条件ですので、どちらか一方が真となっても、もう一方が偽であればWHERE句の抽出条件としては成立しません。 ここではAND条件で積集合を求めていると考えていただいてもよいかと思います。