Question 112822 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Select all if value exists in column

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;
Tags:

2

Answers


  1. 0

    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
)
    Login or Signup to reply.
  2. 0
    • 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')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search