CODEWITHSUNDEEP
phpsql
Fredric Dael
Fredric Dael

Reputation: 61

How do I use a LIKE or CONTAINS clause in SQL on the same row?

In my project, you can list the ingredients you have and search for recipes that can be made with those ingredients. Here's my sample SQL query: heres the database https://i.sstatic.net/jU8jo.png i want to get the matching list.

SELECT recipes_Name 
FROM tblrecipes
INNER JOIN tblingredients
ON tblrecipes.recipes_ID = tblingredients.ingredients_ID
WHERE ingredients_name = variable1 AND variable2 AND variable3

Why is this query not working?

Upvotes: 1

Views: 99

Answers (1)

George G
George G

Reputation: 7695

You can use in clause:

SELECT recipes_Name 
FROM tblrecipes
INNER JOIN tblingredients
ON tblrecipes.recipes_ID = tblingredients.ingredients_ID
WHERE ingredients_name in (variable1, variable2, variable3)

And my recommendation is to use prepared statements with PDO, by means of security.

UPDATE:

I've made query that will only fetch recipes with all ingredients specified (if even one ingredient is missing for the recipe it will not be included):

select
    rcp.*,
    ingr.selected_ingr_count
from
    tblrecipes rcp inner join(
        select
            ingredients_ID as recipe_id,
            count(*) as selected_ingr_count
        from
            tblingredients
        where
            ingredients_name in(
                'egg',
                'butter',
                'milk',
                'flour'
            )
        group by
            ingredients_ID
    ) as ingr on
    ingr.recipe_id = rcp.recipes_ID
    and ingr.selected_ingr_count =(
        select
            count(*)
        from
            tblingredients
        where
            tblingredients.ingredients_ID = rcp.recipes_ID
    )

This will result:

recipes_ID      recipes_Name        selected_ingr_count
        1       cake                3
        3       brownies            2

Upvotes: 2

Related Questions