関連テーブルの検索方法

質問

以下のテーブル関連で、以下のロジックで、recipe_id だけの一覧を取得するのは、1本のSQLではどのようにすればよいでしょうか？
＃あとこのような、クエリーの事をなんと呼べばいいのでしょうか…。
＃適切質問タイトルが思い浮かばなかったので、提示してもらえるとありがたいです。

recipe
recipe_id | name
1 | ズッキーニとオリーブのトマトパスタ
2 | ズッキーニとオリーブのトマトのマリネ
3 | ズッキーニのアリオリ
4 | 野菜のフリッター

recipe_ingredients
recipe_ingredient_id | recipe_id | master_id
1 | 1 | 1 | ※　ズッキーニとオリーブのトマトパスタ
2 | 1 | 2 | ※　ズッキーニとオリーブのトマトパスタ
3 | 1 | 3 | ※　ズッキーニとオリーブのトマトパスタ
4 | 1 | 4 | ※　ズッキーニとオリーブのトマトパスタ
5 | 2 | 1 | ※　ズッキーニとオリーブのトマトのマリネ
6 | 2 | 2 | ※　ズッキーニとオリーブのトマトのマリネ
7 | 2 | 3 | ※　ズッキーニとオリーブのトマトのマリネ
8 | 3 | 1 | ※　ズッキーニのアリオリ
9 | 3 | 4 | ※　ズッキーニのアリオリ
10 | 4 | 1 | ※　野菜のフリッター

※　はわかりやすくつけただけで、実際のテーブルデータではないです。
vegetables
vegetable_id | name
1 | ずっきーに
2 | おりーぶ
3 | とまと
4 | にんにく

ロジックはアンド検索で：
検索条件「ズッキーニ、トマト」で検索したら、
　「ズッキーニとオリーブのトマトパスタ」と「ズッキーニとオリーブのトマトのマリネ」
検索条件「にんにく、ズッキーニ」で検索したら、
　「ズッキーニとオリーブのトマトパスタ」と「ズッキーニのアリオリ」

が見つかる。

名前でLike検索はしない。
全く持って想像がつかないので教えてください。

ちょっと考えてみたんですが、↓では、１～４のレシピ全てが見つかってしまうのですよね…。
select recipe_id from recipe
inner join recipe_ingredients using ( recipe_id )
inner join vegetables using ( vegetable_id )
where
vegetables.name like '%ずっきーに%' or
vegetables.name like '%にんにく%' ;

もしかして、以下でしょうか？

select recipe_id from recipe
inner join recipe_ingredients using ( recipe_id )
inner join vegetables using ( vegetable_id )
where
vegetables.name like '%にんにく%'
INTERSECT
select recipe_id from recipe
inner join recipe_ingredients using ( recipe_id )
inner join vegetables using ( vegetable_id )
where
vegetables.name like '%ずっきーに%';

なんとなくコストが高そうなので、他に方法はないでしょうか？

回答No.1

chukenkenkou · ベストアンサー

recipe_ingredients表のmaster_id列と、vegetables表のvegetable_id列が対応しているのですね？
recipe表の野菜の名前はカタカナですが、vegetables表のname列はひらがななのですか？

などなど、いろいろと引っ掛かる点があるのですけどね。

SQL例を示しますから、実行結果が期待通りか確認すると共に、どういうことをやっているか調べてみてください。

select tR.*
from tR
inner join
(select R_id
from tRI
inner join tV
on M_id=V_id
where name in('ズッキーニ','トマト')
group by R_id
having count(*)=2) as x
on tR.R_id=x.R_id
order by tR.R_id

お礼

ありがとうございます！

having って一度も使ったことありませんでした…。
group by した結果に対して、条件で絞り込むことができるんですね！

二つのVegetablesの名前で検索したら、
２行のrecipe_ingredientsが引っかかったものが全てに
合致したもので、あるから、recipe_id を Group by でカウントを取った
結果に対して、絞り込むと言うことですよね？

ご指摘いただいたとおり、
[recipe_ingredients.master_id と vegetables.vegetable_id
が対応]で正しいです。
すみませんサンプル作るときにカラム名を間違えてしまったようです。

vegetablesについても、
ずっきーに、おりーぶ、とまと はカタカナのつもりになっていました。
ご指摘の通りです。すみません。

情けない不備がたくさんあったのに、
回答いただいてありがとうございました！
助かりました。