I have a simple Ruby on Rails app CookBook, with recipes. I have 2 tables (recipes and ingredients). One recipe has many ingredients.

I am trying to implement a simple search box so that I can filter recipes containing certain ingredients....

Here is my issue: if I type in search box two or more ingredients, I get more rows, but I think the result set should be shorter, because the more ingredients I type in the search box, the narrower my search should be....

These are the tables:

create table recipes (id int, recipe_name varchar(100));

create table ingredients(id int, ingredient_name varchar(100), recipe_id int);

insert into recipes values 
(1, 'my recipe 1'),
(2, 'my recipe 2');
(3, 'my recipe 3');
(4, 'my recipe 4');

insert into ingredients values
(1, 'Banana', 1), 
(2, 'Milk', 1),
(3, 'Banana', 2), 
(4, 'Milk', 2),
(5, 'Apple', 2);
(6, 'Flour', 4),
(7, 'Lemon', 4);
(8, 'Lemon', 3),
(9, 'Milk', 3);
(10, 'Banana', 4);

Let's say I type in search box two ingredients, for example "banana" and "milk". Then I am expecting to get recipes with id 1 and 2 in my result set, because both of those recipes contain 'banana' and 'milk' However, there is a recipe id 4 with an ingredient 'banana'. Should it be displayed too?

This is the query I currently have:

SELECT DISTINCT "recipes".* 
FROM "recipes" 
LEFT  JOIN "ingredients" ON 
"ingredients"."recipe_id" = "recipes"."id" 
WHERE (ingredient_name LIKE '