Crafting database SQL conundrum
Ok, so while working on the crafting system for EverSky, I came across this SQL issue and wanted to share it:
I’m trying to create an SQL based crafting system, here are the current tables relevent to the discussion:
So clearly, there is a one to many relationship between recipe and recipeingredients, plus there is a one to many relationship between recipe and reciperesults. The crafting ingredients are stored in the crafting ingredients table for convenience, they are basically the items (and counts) current displayed in the craft slots area of the crafting UI.
So the required functionality is that given the items in the craftingingredients data, find all recipe id’s that contain EXACTLY the same ingredients in the RecipeIngredients and ONLY those id’s. So basically exclude any recipes which have any fewer ingredients, any more ingredients, or any ingredients not in the craftingingredients rows.
What I currently have is:
SELECT recipeingredients.recipe, items.itemname, recipeingredients.item AS Item, recipeingredients.Count AS [Count]
FROM items INNER JOIN (recipeingredients INNER JOIN craftingredients ON (recipeingredients.item=craftingredients.item) AND (recipeingredients.count=craftingredients.count)) ON items.ID=recipeingredients.item
ORDER BY recipeingredients.recipe;
Which currently returns all of the rows for any recipe which has ANY matching ingredients. So if you have one of the ingredients in the craftingredients table then the row is returned, which isn’t really cutting it.
Now I do have a fallback here, in that I can simply concatenate all of the itemid+count at recipe design time into a big old string blob and then insert that into a simple recipe row with id and blob, then the lookup simply becomes a text comparison, which should work reasonably quickly. But its not very SQL is it?
Here is a link to the access 2007 db file for anyone who wants to have a try and play with the issue. There’s a form in there to add recipes (I plan on using access because it has pretty nice user interface stuff for forms design). Final thing uses SQLite though.