Query to select a row from a column that matches "x but not y" where y is everything else-CodePudding

I'm trying to write a query that will select rows that match only what I'm looking for. If the row has other stuff, then I don't want it. The column is a varchar field and the values in the column are a comma delimited string.

So here is the dilemma: The table has a recipe column and an ingredients column. Like this:

Muffin | "salt"
Cake | "salt,pepper"
Pie | "salt,pepper,butter"

In my query I want to find all of the recipes that contain ANY COMBINATION of salt and/or pepper but nothing else.

If I write the query like this:

select recipe
from mytable
where ingredients like "%pepper%" and/or ingredients like "%salt%"

I want the Muffin and the Cake be returned but not the Pie (because it has additional ingredients that are not specifically listed in the search criteria). How do I write the exclusion?

I'm using SQL server 2008

CodePudding user response：

select recipe
from T cross apply string_split(ingredients, ',')
group by recipe
having count(case when value     in (<my list>) then 1 end) > 0
   and count(case when value not in (<my list>) then 1 end) = 0

CodePudding user response：

You've already received comments encouraging you to consider a different design for your schema and the rationale for this so I'll only focus on a suggestion for your schema here.

You may consider using REPLACE to determine if the column of ingredients will be empty or whether this recipe has no other ingredients. The LIKE was used to determine whether the recipe had the desired ingredients.

Approach 1

SELECT 
    recipe,
    ingredients
FROM mytable
WHERE (
   CONCAT(',',ingredients,',') LIKE '%,salt,%' OR
   CONCAT(',',ingredients,',') LIKE '%,pepper,%'
) AND
   REPLACE(REPLACE(REPLACE(ingredients,'salt',','),'pepper',','),',','')=''

View working demo online

Approach 2

Ingredients that are only desired are placed in a subquery and filtered using the left join. The having clause is then used to determine whether the list of ingredients only has these ingredients .

SELECT recipe
FROM (
    SELECT 
        recipe,
        ingredients,
        desired
    FROM 
        mytable m
    LEFT JOIN (
        SELECT 'salt' as desired UNION ALL
        SELECT 'pepper'
    ) d ON CONCAT(',',ingredients,',') LIKE CONCAT('%,',d.desired,',%')
) t
GROUP BY
      recipe
HAVING
    LEN(
        MAX(
          REPLACE(ingredients,',','')
        )
    ) <= SUM(LEN(desired))

View working demo online

CodePudding user response：

You really should have 3 tables for this solution. You are suffering from the X Y Problem

Your solution for example should look something like this:

product

product_id	name
1	Muffin
2	Cake
3	Pie

ingredient

ingredient_id	name
1	Salt
2	Pepper
3	Butter

ingredient_to_product

product_id	ingredient_id
1	1
2	1
2	2
3	1
3	2
3	3

Then you can simply write your query based on your positive ingredient list and not worry about what they DON'T HAVE: According to your original statement: "in my query I want to find all of the recipes that contain ANY COMBINATION of salt and/or pepper but nothing else." That can be accomplished using the IN operator

SELECT a.name FROM product a
    LEFT JOIN ingredient_to_product b
    ON a.product_id = b.product_id

    LEFT JOIN ingredient c
    ON b.ingredient_id = c.ingredient_id
WHERE c.name IN ('salt','pepper');

And conversely you can exclude with NOT IN --

WHERE c.name IN ('salt','pepper') AND c.name NOT IN ('milk', 'butter');

OR to include salt and pepper -- But exclude every other possibility .. Use a nested SELECT to exclude everything BUT salt, and pepper

WHERE c.name IN ('salt','pepper') 
AND c.name NOT IN (
    SELECT c.name FROM product a
        LEFT JOIN ingredient_to_product b
        ON a.product_id = b.product_id

        LEFT JOIN ingredient c
        ON b.ingredient_id = c.ingredient_id
    WHERE c.name NOT IN ('salt','pepper')
    GROUP BY c.name
)
GROUP BY a.name;