Sqlite3 Subquery where ALL records in subquery are true

Question

I am looking for a solution that keeps me from looping through each record checking if ALL the sub-records match the required criteria.

Using other SQL languages, I could query using ALL, but sqlite does not support "where field = ALL ( select subquery )" syntax.

I have 3 tables. One, a list of recipes. Two, a list of recipe ingredients. Three, a table that users will toggle values on and off to select the correct recipe(s).

I need to display all the recipes that contain "ALL" the ingredients that the user checks off.

So, if the user selects "green pepers" and also "chicken", only recipes that contain BOTH will be displayed.

Table 1: Recipes
recipe_id   int
recipe_name text

Table 2: Ingredients
recipe_header     int ( link to header)
recipe_ingredient text

Table 3: User selection ( this table will be modified on the fly to represent only ingredients that are included in recipes as different ingredients are checked off)
recipe_ingredient   text
user_selected       text (true/false)

Table 1:
t1_id       t1_name
---------   ---------------
1       Chicken Marsala
2       Chicken Parmesan
3       Flank Steak
4       Grilled Salmon

Table 2:
t2_link     t2_ingredient
-------     -------------
1       chicken
1       green peppers
1       olive oil
2       chicken
2       olive oil
2       peeled tomatoes
3       flank steak
3       olive oil
3       soy sauce
3       pepper
4       salmon fillet
4       soy sauce
4       pepper

Table 3:
t3_ingredient       t3_checked
-------------       ----------
chicken         ( true or false )
flank steak     ( true or false )
green peppers       ( true or false )
olive oil       ( true or false )
peeled tomatoes     ( true or false )
pepper          ( true or false )
salmon fillet       ( true or false )
soy sauce       ( true or false )

If the user selects the following ingredients (true) "chicken", "olive oil"

-- Chicken Marsala & Chicken Parmesan will be displayed.

If the user selects "soy sauce" and "pepper"

-- Flank Stean & Grilled Salmon would be displayed.

If the user selects "olive oil"

-- Chicken Marsala, Chicken Parmesan & Flank Steak would be displayed.

Answer 1

One of the way to do this as it is demonstrated in this SQL Fiddle:

select * from Table1
where t1_id IN (
  SELECT t2_link 
  FROM Table2 INNER JOIN Table3 ON 
    t2_ingredient = t3_ingredient AND t3_checked = 'true'
  GROUP BY t2_link
  HAVING COUNT(DISTINCT t2_ingredient) = (
    SELECT COUNT(t3_ingredient) FROM Table3 WHERE t3_checked = 'true'
    )
)

Results for "chicken", "olive oil"

| t1_id |          t1_name |
|-------|------------------|
|     1 |  Chicken Marsala |
|     2 | Chicken Parmesan |