坂井さん、ありがとうございます。

行数で絞り込むという方法ですか。
なるほど、目から鱗です。

実際のテーブルはカラム名やデータが違いますが、例示していただいたテーブル構造でいえば
SELECT y.id, y.ZAI, y.IRO, COUNT(*) 
FROM YOKI y LEFT JOIN NAIYO n ON y.id=n.YOKI_ID
WHERE (BINARY n.BUTU LIKE 'りんご' OR BINARY n.BUTU LIKE 'み%') AND y.IRO = '無色透明'
GROUP BY y.id  
HAVING COUNT(*) >= 2
と少しだけアレンジして試しました。
BUTUを部分一致検索もしてみたくて LIKE で検索式を作って
部分一致検索(み%)だと「みかん」も「みそ」も該当するのでHAVING は =2 ではなく>=2 に

YOKIに相当するテーブルが７万行、NAIYOに相当するテーブルが150万行ありますが、
1秒前後で結果が返ってきて、思った以上に速くて感動です。
phpのforループで該当するYOKI_IDを抽出してそれをWHEREに入れてまた抽出する従来の方法と違って
１回のSQLで結果が出せるのでphpスクリプトを書き換えずにMySQLに直接コマンド打って
簡単に試せるのも助かりました。

ただ、部分一致ができるようにLIKEを使うと「りんご」がなくても
「みかん」と「みそ」があれば行数の条件を満たしてしまうのでここは考えどころです。
坂井さんご指摘の通り、提示した条件があいまいだったところが仇になりました。
部分一致をあきらめれば簡単に解決できますが、
とても参考になる書き方を教えていただいたのでここからもっと検討してみます。


・必ず2種類のものを指定するのか、3種類以上固定なのか、3種類以上可変なのか
＞０種以上可変です。webアプリなので０種の場合、１種以上の場合など条件によってSQL文を組み立てます。
・3種類以上の場合「その全てが含まれる」という条件にしたいのか、「それらの中で複数が含まれている」ものを欲しいのか
＞検索画面でそのどちらかを選べるようにしてます。これはHAVINGがあればブツはAND検索、HAVINGがなければブツはOR検索になるんじゃないかと思いました。
・そもそも2種を例にしているけど、実際には20とか30とか指定するんじゃないか
＞実際に作っているwebアプリはブツに相当する部分は５種類まで指定できるように作ってます。それほどブツの種類は多くならない想定です。
・指定する条件は ブツ(複数)と容器色だけなのだろうか
＞実際に作っているのはNAIYOに相当するテーブルはブツ(複数)だけ、YOKIに相当するテーブルには６つのカラムがあるのでこっちのテーブルに対して任意の数の条件がAND指定で加わります。これは坂井さんのアドバイスにあるとおりWHEREにANDで条件を追加すれば大丈夫ですね。



> 2020/04/28 19:18、SAKAI, Kei <sakaik@xxxxxxxxxx>のメール:

> 

> 坂井です。

> 

> 　久光さん、こんにちは。

> こんな感じでどうですか。

> 

> 

> ■動作確認要テーブルとデータ：

> CREATE TABLE YOKI (ID INTEGER, ZAI VARCHAR(10), IRO VARCHAR(10));

> INSERT INTO YOKI VALUES (1, 'PET', '黒'),(2, 'PET', '無色透明'),

> (3, 'ガラス', '無色透明'),(4, 'ガラス', '黄色透明'),(5, '塩ビ', '白');

> 

> CREATE TABLE NAIYO (YOKI_ID INTEGER, BUTU VARCHAR(20));

> INSERT INTO NAIYO VALUES (1, 'りんご'),(2, 'りんご'),(3, 'りんご'),(4,

> 'りんご'),(2, 'みかん'),(4, 'みかん'),(5, 'みかん'),(1, '梨'),(2, '梨'),(4, '梨');

> 

> 

> ■やり方の一例：

> 

> SELECT y.ID, y.ZAI, y.IRO, COUNT(*)

> FROM YOKI y LEFT OUTER JOIN NAIYO n ON (y.ID=n.YOKI_ID)

> WHERE  n.BUTU IN ('りんご', '梨')

>  AND  y.IRO IN ('無色透明')

> GROUP BY y.ID, y.ZAI, y.IRO

> HAVING COUNT(*) = 2;

> 

> ■「やり方の一例」の考え方の解説：

> 

> まず、容器テーブルとブツテーブルを結合します。容器テーブルの右側にブツをくっつける、というイメージですね。

> 

> SELECT y.ID, y.ZAI, y.IRO, n.BUTU

> FROM YOKI y LEFT OUTER JOIN NAIYO n ON (y.ID=n.YOKI_ID)

> ORDER BY y.ID;

> +------+-----------+--------------+-----------+

> | ID   | ZAI       | IRO          | BUTU      |

> +------+-----------+--------------+-----------+

> |    1 | PET       | 黒           | りんご    |

> |    1 | PET       | 黒           | 梨        |

> |    2 | PET       | 無色透明     | りんご    |

> |    2 | PET       | 無色透明     | みかん    |

> |    2 | PET       | 無色透明     | 梨        |

> |    3 | ガラス    | 無色透明     | りんご    |

> |    4 | ガラス    | 黄色透明     | りんご    |

> |    4 | ガラス    | 黄色透明     | みかん    |

> |    4 | ガラス    | 黄色透明     | 梨        |

> |    5 | 塩ビ      | 白           | みかん    |

> +------+-----------+--------------+-----------+

> 

> 

> で、その中で欲しい条件に合致する行だけを抽出します。

> ここでは、「りんご」の行と「梨」の行が欲しいので、WHERE条件に書きます。

> （IRO条件も追加したければ、AND/OR駆使して指定すれば良いです。今回の最終目的のためには AND  y.IRO IN ('無色透明')

> という条件を追加すれば良いです）

> 

> 

> SELECT y.ID, y.ZAI, y.IRO, n.BUTU

> FROM YOKI y LEFT OUTER JOIN NAIYO n ON (y.ID=n.YOKI_ID)

> WHERE  n.BUTU IN ('りんご', '梨')

> ORDER BY y.ID;

> +------+-----------+--------------+-----------+

> | ID   | ZAI       | IRO          | BUTU      |

> +------+-----------+--------------+-----------+

> |    1 | PET       | 黒           | りんご    |

> |    1 | PET       | 黒           | 梨        |

> |    2 | PET       | 無色透明     | りんご    |

> |    2 | PET       | 無色透明     | みかん    |

> |    2 | PET       | 無色透明     | 梨        |

> |    3 | ガラス    | 無色透明     | りんご    |

> |    4 | ガラス    | 黄色透明     | りんご    |

> |    4 | ガラス    | 黄色透明     | 梨        |

> +------+-----------+--------------+-----------+

> 

> 

> これを、IDごとにグルーピングして、「りんご」「梨」の両方を含む、つまりその数が2であるものを得れば良いのです。

> （実際には、ID, ZAI, IRO は一体なので、この3つをGROUP BYに（私なら）指定します）

> 

> 　数で絞る前の結果は、こんな感じ。

> 

> SELECT y.ID, y.ZAI, y.IRO, COUNT(*)

> FROM YOKI y LEFT OUTER JOIN NAIYO n ON (y.ID=n.YOKI_ID)

> WHERE  n.BUTU IN ('りんご', '梨')

> GROUP BY y.ID, y.ZAI, y.IRO;

> +------+-----------+--------------+----------+

> | ID   | ZAI       | IRO          | COUNT(*) |

> +------+-----------+--------------+----------+

> |    1 | PET       | 黒           |        2 |

> |    2 | PET       | 無色透明     |        2 |

> |    3 | ガラス    | 無色透明     |        1 |

> |    4 | ガラス    | 黄色透明     |        2 |

> +------+-----------+--------------+----------+

> 

> ここから COUNT(*) の数で絞るのは、(WHEREではなく) HAVING を使います。

> (今は容器の色の条件を指定していないので、ID=1,2,4 が該当しますが、WHERE条件で容器条件を「無色透明」と指定していれば、2 のみが残りますね）

> 

> 

> 

> ■要件を伝える大切さ

> 

>> 「りんごと梨の両方が入っている無色透明の容器」のような

> 

> と書かれている「のような」の部分が、とても曖昧です。

> 

> ・必ず2種類のものを指定するのか、3種類以上固定なのか、3種類以上可変なのか

> ・3種類以上の場合「その全てが含まれる」という条件にしたいのか、「それらの中で複数が含まれている」ものを欲しいのか

> ・そもそも2種を例にしているけど、実際には20とか30とか指定するんじゃないか

> ・指定する条件は ブツ(複数)と容器色だけなのだろうか

> 

> などなど、読み手の想像力が試されます。

> 曖昧な要件だと、一旦の回答を差し上げたあとで「いや、実は条件が少し違って・・・」と後出しすることになり、お互いに掛けた手間の割には、なかなかゴールにたどり着けないというアンハッピーな状態になることが多いですね。

> 

> （ええ、実際、面白そうなテーマだったので、あれこれ妄想を働かせました(笑）。キライじゃないんですけど、「で、正解の要件は何だったの？」と気になります）

> 

> 

> 2020年4月28日(火) 18:18 Hisamitsu Issei <DS1I-HSMT@xxxxxxxxxx>:

>> 

>> 久光と申します

>> 

>> 

>> phpからMySQLに検索をかけているスクリプトの一部に強引なやりかたのせいもあって検索に時間がかかっている部分があります。

>> とりあえず動いてはいるのですが、

>> もっとスマートで高速に検索できるSQLの書き方があるのではないかと相談させていただきます。

>> 

>> (1)容器に関する情報をまとめたテーブル

>> (2)容器の中に入っている物品の名前を記録したテーブル

>> が存在したとします。

>> 

>> 容器テーブル

>> ＝＝＝＝＝＝＝＝＝

>> 容器ID　材質　色

>> ＝＝＝＝＝＝＝＝＝

>> 1 PET 黒

>> 2 PET 無色透明

>> 3 ガラス 無色透明

>> 4 ガラス 黄色透明

>> 5 塩ビ 白

>> ＝＝＝＝＝＝＝＝＝

>> 

>> 内容物テーブル

>> ＝＝＝＝＝＝＝＝

>> 容器ID　内容物名

>> ＝＝＝＝＝＝＝＝

>> 1 りんご

>> 2 りんご

>> 3 りんご

>> 4 りんご

>> 2 みかん

>> 4 みかん

>> 5 みかん

>> 1 梨

>> 2 梨

>> 4 梨

>> ＝＝＝＝＝＝＝＝

>> 

>> 実際には、上記の容器テーブルに相当する実際のテーブルは７万行ほどあり、内容物テーブルに相当する実際のテーブルは１５０万行ほどあるデータです。

>> 

>> SQLの書き方に悩んでいるのは、

>> 「りんごと梨の両方が入っている無色透明の容器」のような、複数の内容物を全て含む該当容器を探すときです。

>> 現在は、phpのfor文でいったん

>> SELECT `容器ID` FROM `内容物` WHERE `内容物名` = 'りんご'

>> SELECT `容器ID` FROM `内容物` WHERE `内容物名` = '梨'

>> と検索を繰り返して、得られた結果を整理して該当する容器IDを配列にまとめた上で、最後に

>> SELECT * FROM `容器` WHERE `色` = '無色透明' AND ( `容器ID`=2 OR `容器ID`=4 ... )

>> というように 容器テーブル を検索する方法をとってます。

>> 条件によっては( `容器ID`=2 OR `容器ID`=4 ... ) の部分が数千から数万もの数になることもあり、

>> inner joinやleft joinなどの結合やUNIONなどなにかもっとスマートに高速に検索できるSQLの書き方があるんじゃないかと思うのですが

>> どうでしょうか。

>> 

>> 「りんごまたは梨」のor検索なら容器テーブルと内容物テーブルをleft joinして容器IDでgroup byすればいいと思うのですが

>> 「りんごと梨」のand検索となると、どうしていいのかわからなくなってます。