Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Development Tools  Other Development Tools  Recipe Table and Inventory Table

Author  Topic  Dargonis Starting Member 1 Post Posted - 2004-06-23 : 06:04:45 Hello, I'm trying to write an application with Cold Fusion in Access which basically takes a table of Recipes with required ingrediants and compares it to a table of available inventory to show me what I can make based on what I have. So its something like this: Table Name is "Recipe" Recipename ingred1 Quant1 ingred2 Quant2 ingred3 Quant3 ingred4 .... The Second table (in the same database Table Name is "Resources" Inventoryname Quantity etc etc Where I run into my problem is that nearly all the inventoryname has multiple words.. so for example, ingred1 might be flour, but the inventoryname is Whole Wheat Flour or an ingrediant might have two words but inventory only one. Then secondly, only 1 record ever comes up, like its not looping through to match up the inventory to the recipies ingrediants. Its just checking the first row of these tables. My SQL looks something like: SELECT RESOURCE.Inventoryname, Recipe.Recipename, Recipe.ingred1, Recipe.ingred2, Recipe.ingred3, Recipe.ingred4 FROM RESOURCE, Recipe WHERE RESOURCE.Inventoryname LIKE '%<cfoutput>#Recipe.ingred1#</cfoutput>%' (and this repeated for each of the ingrediants then where the separate query matches occur I use Cold Fusion to evaluate the individual matches and figure out if we have enough to make the Recipe. (a-hem in theory) Is there a way in sql to match all LIKE inventory to any of the seperate ingred fields in one query? Instead of doing one ingrediant at a time and then using Cold Fusion to make the match. Seems to me SQL could do this without breaking a sweat. (unlike me.) When you have a variable that needs to be filled in a WHERE clause, how do you get each record to loop on through it instead of it just reading the first record?