Consider the following.
I have a table with RecipeComponents , which has a one to many relationship to Ingredients.
I want the names of recept_navn from table Recepter which do not include the Raavare champignon.
Recept has a one to many relationship with receptkomponent. One recept has many receptkomponents.
Receptkomponent has a one to many relationship with Raavare. A Raavare is included in multiple receptkomponents.
Initially i thought i would just select and join the three tables and make a where clause for raaavare_navn != Mushroom. Since it is a one to many relationship and the RecipeComponent table contains multiple ingredients, i'll end up deleting the row with mushroom, but the RecipeComponent will still show up in rows with the other ingredients
Here is what i tried to do at first.
SELECT DISTINCT recept.recept_id, recept_navn
FROM recept
JOIN receptkomponent ON recept.recept_id = receptkomponent.recept_id
JOIN raavare ON raavare.raavare_id = receptkomponent.raavare_id
WHERE raavare.raavare_navn != 'champignon'
GROUP BY recept_id
DATA:
Tablename: Raavare
raavare_id | raavare_navn | leverandoer
___________________________________________________
1 | dej | Wawelka
2 | tomat | Knoor
3 | tomat | Veaubais
4 | tomat | Franz
5 | ost | Ost og Skinke A/S
6 | skinke | Ost og Skinke A/S
7 | champignon | Igloo Frostvarer
Tablename: Receptkomponent
recept_id | raavare_id | nom_netto | tolerance
__________________________________________________________________
1 | 1 | 10.0 | 0.1
1 | 2 | 2.0 | 0.1
1 | 5 | 2.0 | 0.1
2 | 1 | 10.0 | 0.1
2 | 3 | 2.0 | 0.1
2 | 5 | 1.5 | 0.1
2 | 6 | 1.5 | 0.1
3 | 1 | 10.0 | 0.1
3 | 4 | 1.5 | 0.1
3 | 5 | 1.5 | 0.1
3 | 6 | 1.0 | 0.1
3 | 7 | 1.0 | 0.1
Tablename: Recepter
recept_id | recept_navn
_____________________________
1 | margherita
2 | prosciutto
3 | capricciosa
I figured out the answer.
First you want to make a query that selects all the recept_names with the raavare 'champignon'. Then you make a query that selects all recept_names where the previous query is not in.
SELECT DISTINCT recept_navn
FROM recept
WHERE recept_navn NOT IN
(
SELECT DISTINCT recept_navn
FROM recept
JOIN receptkomponent
ON receptkomponent.recept_id = recept.recept_id
JOIN raavare
ON raavare.raavare_id = receptkomponent.raavare_id
WHERE raavare_navn = 'champignon'
)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments