[MySQL]Select op meerdere waarden in zelfde kolom

[XxKr0n3xX]

Ik loop tegen wat problemen aan met mijn database setup en een mysql query. Ik wil een simpele app in elkaar zetten waarbij ik ingredienten bij elkaar kan klikken en daar vervolgens 1 of meerdere recepten tevoorschijn komen.

Onderstaand mijn database tabellen.

recipes
recipeID (INT auto_increment PK)
recipeName(VARCHAR)
recipeDescription(TEXT)
recipeKitchenID(INT)
recipeCategoryID(INT)
dateCreated(DATETIME)
dateUpdated(DATETIME)
userID(INT)

Ik heb een tabel waarin ik losse ingredienten en dergelijke wil opslaan

ingredients
ingredientID(INT auto_increment PK)
ingredientName(VARCHAR)
ingredientDescription(VARCHAR)

Daarbij heb ik een tabel waarmee de ingredienten aan een recept koppel

ingredientsRecipes
ingredientRecipeID(INT)
recipeID(INT) (verwijzend naar recipes)
ingredientID(verwijzend naar ingredients)
amount(INT)
amountMeasurementType(INT)

Ik wil een query uitvoeren waarin ik kan zeggen. Geef mij alle recepten waar eieren,bloem en water in voorkomen.
Wanneer ik echter de volgende query uitvoer:

code:

SELECT * FROM ingredientsRecipes WHERE ingredientID = 1 AND ingredientID = 2 GROUP BY recipeID

Krijg ik natuurlijk niks terug omdat er nergens zowel 1 als 2 in de ingredientID kolom zal voorkomen.
Kunnen jullie me misschien in de juiste richting wijzen hoe ik een query kan uitvoeren waarbij ik de rijen terug kan krijgen waarbij zowel 1 als 2 gevonden worden? Als het databaseontwerp anders moet dan hoor ik dat natuurlijk ook graag.

[disjfa]

SQL:

ingredientID IN (1,2)

[Springuin]

Ff uit de losse pols; iets als:

code:

select * from (select count (*) as aantalvoorkomend, recipeid from ingredientsRecipes where ingredientID IN (1,2) group by recipeid) where aantalvoorkomend = 2

[P_de_B]

WHERE ingredientID IN (1,2,3,4,5)

of

WHERE ingredientID = 1 OR ingredientID = 2

Let er wel op dat als je gaat uitbreiden met extra selectiecriteria je met haakjes moet gaan werken. ALs je bijvoorbeeld alle recepten wilt hebben waar amount = 100 (stom voorbeeld):

WHERE Amount = 100 AND (ingredientId =1 OR ingredientId = 2)

[Hardfreak]

Ik ben weliswaar geen database-genie, maar je kan je in je ingredientID niet comma seperated waarde invullen met alle ingredienten ? Dan kan je evt. met een WHERE LIKE iets tevoorschijn toveren.

Nu, als het selecteren van ingredienten en daaruit een recept toveren de hoofdfunctie van je tabel is zou ik het ontwerp misschien omdraaien. Dus in plaats van bij je recepten te vermelden welke ingredienten erin zitten, bij je ingredienten zetten welk recept dit ingredient gebruikt.

[XxKr0n3xX]

Het werkt! Hartstikke bedankt.

[GlowMouse]

Hardfreak schreef op vrijdag 07 augustus 2009 @ 17:12:
Ik ben weliswaar geen database-genie, maar je kan je in je ingredientID niet comma seperated waarde invullen met alle ingredienten ? Dan kan je evt. met een WHERE LIKE iets tevoorschijn toveren.

Word eerst maar eens een database-genie, dit is geen goed idee.

[NMe]

XxKr0n3xX schreef op vrijdag 07 augustus 2009 @ 17:13:
Het werkt! Hartstikke bedankt.

Ik hoop dat je de oplossing van Springuin gebruikt hebt? Want dat is de enige oplossing hier in het topic die zowel die ID's opzoekt en ook controleert of ze echt beiden in dat recept zitten. Ik zou hem zelf wel anders geschreven hebben:

SQL:

SELECT COUNT(recipeID) AS aantalvoorkomend, recipeID
FROM ingredientsRecipes
WHERE ingredientID IN (1,2)
GROUP BY recipeID
HAVING COUNT(recipeID) = 2

Maar da's personal preference.

[ Voor 26% gewijzigd door NMe op 07-08-2009 18:38 ]

[XxKr0n3xX]

Ik gebruik de oplossing van springuin. Ik weet dat het sowieso een een op meer moest worden en met name om ook tellingen en beheersbaarheid ten goede te komen zou ik niet voor een "geserialiseerde" opslag kiezen.

Ik moest alleen uitvinden hoe ik dan de query herhaaldelijk op dezelfde kolom kon uitvoeren om dan toch gewoon de gerechten er uit te krijgen waar rund,soja en zout inzitten.(voorbeeld).

[Hardfreak]

GlowMouse schreef op vrijdag 07 augustus 2009 @ 17:18:
[...]

Word eerst maar eens een database-genie, dit is geen goed idee.

kan er mss wat uitleg bij geven ?

Anyway, 'k had een klein detail over 't hoofd gezien waardoor de andere oplossingen dus wel de logische zijn en die van mij eigenlijk zo brak gelijk de pest

[.oisyn]

Hardfreak schreef op vrijdag 07 augustus 2009 @ 18:21:
[...]


kan er mss wat uitleg bij geven ?

Het is niet onderhoudbaar en het is takketraag

[NMe]

Hardfreak schreef op vrijdag 07 augustus 2009 @ 18:21:
[...]

kan er mss wat uitleg bij geven ?

Geen betere uitleg dan een praktijkvoorbeeldje.

Schrijf voor mij eens query's die de volgende twee dingen doen:

• Haal alle ingrediënt-records op behorend bij recept met ID 1.
• Haal alle recepten op waar ingrediënten 1 en 5 in zitten.

Beiden gaan je in jouw oplossing niet in een enkele query lukken terwijl het met een genormaliseerde (zoekwoordje, doe 's Googlen ) tabelstructuur beiden heel goed mogelijk is in één enkele snelle query.

[Hardfreak]

NMe schreef op vrijdag 07 augustus 2009 @ 18:26:
[...]
Beiden gaan je in jouw oplossing niet in een enkele query lukken terwijl het met een genormaliseerde (zoekwoordje, doe 's Googlen ) tabelstructuur beiden heel goed mogelijk is in één enkele snelle query.

Wel kijk, daar heb je dus dat kleine detail dat ik over het hoofd heb gezien.

[NMe]

Hardfreak schreef op vrijdag 07 augustus 2009 @ 18:42:
[...]

Wel kijk, daar heb je dus dat kleine detail dat ik over het hoofd heb gezien.

Ik denk niet dat er veel mensen in dit forum zullen zijn die dat een "klein detail" noemen, maar ok.

Anyway, in short: de hele reden dat je een relationele database gebruikt is dat je het soort kunstgrepen dat je voorstelde juist niet hoeft toe te passen.

[Hardfreak]

NMe schreef op vrijdag 07 augustus 2009 @ 18:44:
[...]

Ik denk niet dat er veel mensen in dit forum zullen zijn die dat een "klein detail" noemen, maar ok.

Anyway, in short: de hele reden dat je een relationele database gebruikt is dat je het soort kunstgrepen dat je voorstelde juist niet hoeft toe te passen.

dat was dan ook zeer sarcastisch bedoeld . Ik heb vaak nog de neiging om te veel te denken in rijen (alle ingredienten van 1 recept moeten op 1 rij), ipv kolommen

[NMe]

Hardfreak schreef op vrijdag 07 augustus 2009 @ 23:21:
[...]

dat was dan ook zeer sarcastisch bedoeld . Ik heb vaak nog de neiging om te veel te denken in rijen (alle ingredienten van 1 recept moeten op 1 rij), ipv kolommen

Nou, de ingrediënten van zo'n recept moeten ook absoluut niet elk een kolom krijgen in een tabel. Ze krijgen juist elk een rij in een koppeltabel.

[cariolive23]

Ik heb vaak nog de neiging om te veel te denken

Niet doen, ga gewoon normaliseren. En onthou dat je nooit en te nimmer meerdere gegevens samen in één kolom in één record zet. Wil je dat toch doen, pak dan eerst een groot geweer en schiet in je voet. Dan weet je ook hoe dat voelt, dat is namelijk wat je doet wanneer je meerdere gegevens samen in één kolom zet.

Uitzondering: Er zijn databases waarmee je array's kunt aanmaken (o.a. PostgreSQL), dan kun je meerdere gegevens georganiseerd samen in één kolom zetten. Dit heeft wel tot gevolg dat je niet meer met "normale" SQL de gegevens kunt opvragen, je moet dan met de array-functies van je database aan de slag. Stop dit soort mogelijkheden weg achter views en stored procedures, applicaties en programmeurs hebben hier grote moeite mee.

SQL:

SELECT UNNEST('{aap, noot, mies}'::text[]); -- dus een text-array

Resultaat:

aap
noot
mies

3 records van het type text.

[o_f_course]

Ik wil een query uitvoeren waarin ik kan zeggen. Geef mij alle recepten waar eieren,bloem en water in voorkomen.

Volgens mij krijg je met een 'where ingredients in (1,2)' niet de recepten waar beiden ingredienten inzitten, maar alle recepten waar of 1 of 2 of beiden zitten. Dit kan dus nooit de oplossing voor het oorspronkelijke probleem zijn.

[Big Womly]

NMe schreef op vrijdag 07 augustus 2009 @ 17:27:
[...]

Ik hoop dat je de oplossing van Springuin gebruikt hebt? Want dat is de enige oplossing hier in het topic die zowel die ID's opzoekt en ook controleert of ze echt beiden in dat recept zitten. Ik zou hem zelf wel anders geschreven hebben:

SQL:

SELECT COUNT(recipeID) AS aantalvoorkomend, recipeID
FROM ingredientsRecipes
WHERE ingredientID IN (1,2)
GROUP BY recipeID
HAVING COUNT(recipeID) = 2

Maar da's personal preference.

SQL:

SELECT recipeID
FROM ingredientsRecipes
WHERE ingredientID = 1
INTERSECT
SELECT recipeID
FROM ingredientsRecipes
WHERE ingredientID = 2

Deze geeft ook alle recepten die beide gebruiken.
Geen idee welke nu de meest performante is...

[sopsop]

Voor twee ingredienten zal het elkaar wellicht niet veel ontlopen. Bij meer ingredienten gaat de intersect methode hard achteruit qua performance.

[T-MOB]

Aan de query met de count ligt wel de aanname ten grondslag dat een ingredient maar een keer per gerecht voorkomt. In de meeste gevallen zal dat geen enkel probleem opleveren, maar ik kan me de situatie voorstellen dat iemand het handig vindt om bijvoorbeeld de 200g suiker voor het deeg apart te noemen van de 2 eetlepels die door de vulling moeten.
Zelf zou ik overigens gewoon joinen:

SQL:

SELECT recipeID FROM Recipes AS R
INNER JOIN ingredientsRecipes AS i1
    ON (i1.recipeID = R.recipeID AND i1.ingredientID = 1)
INNER JOIN ingredientsRecipes AS i2
    ON (i2.recipeID = R.recipeID AND i2.ingredientID = 2)

[o_f_course]

Excuus, had blijkbaar over de count (*)=2 heen gelezen. dat is idd de oplossing.