SQL Select all if value exists in column-CodePudding

I have three Table. The first Table contains a Rezipe with a id, the second Table contains the rezeptid with the ingredients and the third Table shows the ingredient with a restriction(vegan, meat...).

Im looking for a way to select a recipe if the ingredients dont contain a specific restriction and if it contains the restriction then return Null.

I first joined the three tables together and wrote the restriction in the where clausel, but i dont wanna return the recipe, if it contains a specific value

SELECT * 
FROM `rezepte` 
JOIN rezeptezutaten ON rezepte.REZEPTEID = rezeptezutaten.REZEPTEID 
JOIN inhaltsstoffe ON inhaltsstoffe.ZUTATENNR = rezeptezutaten.ZUTATENNR 
WHERE inhaltsstoffe.INHALTSSTOFFEID != 1;

CodePudding user response：

You should be able to get all recipes that contain the ingredient with a particular restriction

select distinct rezeptezutaten.REZEPTEID 
from rezeptezutaten
JOIN inhaltsstoffe ON inhaltsstoffe.ZUTATENNR = rezeptezutaten.ZUTATENNR 
where  inhaltsstoffe.INHALTSSTOFFEID = 1

you can then use this as a subquery

select SELECT * 
FROM `rezepte` 
where REZEPTEID not in (
    select distinct rezeptezutaten.REZEPTEID 
    from rezeptezutaten
    JOIN inhaltsstoffe ON inhaltsstoffe.ZUTATENNR = rezeptezutaten.ZUTATENNR 
    where  inhaltsstoffe.INHALTSSTOFFEID = 1
)

CodePudding user response：

not exactly sure but i believe you need a case when
you can specify the conditions there multiples or single one's where you want to return the null
you can then use this new column

SELECT 
*,
case when restriction in ('vegan','meat') then null else restriction end as new_value

FROM `rezepte` 
JOIN rezeptezutaten ON rezepte.REZEPTEID = rezeptezutaten.REZEPTEID 
JOIN inhaltsstoffe ON inhaltsstoffe.ZUTATENNR = rezeptezutaten.ZUTATENNR 
WHERE inhaltsstoffe.INHALTSSTOFFEID != 1

if you are talking about excluding the row where you have any specific restriction then you can pass the filter directly on the and statement during the join something like


SELECT 
*,
case when restriction like '%vegan%' then null else restriction as new_value
FROM `rezepte` 
JOIN rezeptezutaten ON rezepte.REZEPTEID = rezeptezutaten.REZEPTEID 
JOIN inhaltsstoffe ON inhaltsstoffe.ZUTATENNR = rezeptezutaten.ZUTATENNR 
and restriction NOT in ('meat','vegan')