SQL Server Performance Forum – Threads Archive

A complex SQL Query

Hi everyone, I ‘m new with sql server 2005 and I would like your help with this SELECT query. I am working on a food database (Food names and their ingredients). Many foods in the database have similar names, for example "pepperoni pizza", "vegeterian pizza" and so on. When I search for pizza, I want to be presented with a list all the pizzas, where I can select which one I want to select (to see the ingredients). This is my database diagram <img src=’http://i8.photobucket.com/albums/a21/Hambos/untitled2.jpg’ border=’0′ /> So far, I have this query which presents the food names first and then the ingredients. <pre id="code">Select FoodIngredient From ( Select t_Foods.Food_Name As FoodIngredient, 0 As OrderBy From t_Foods Where Food_Name LIKE ‘%Spaghetti%' Union All Select t_Ingredients.Ingredient_Name, 1 As OrderBy From t_Ingredients Inner Join Foods_Ingredients On t_Ingredients.Ingredient_Id = Foods_Ingredients.Ingredient_ID Inner Join t_Foods On Foods_Ingredients.Food_ID = t_Foods.Food_ID Where upper(t_Foods.Food_Name) Like ‘%Spaghetti%' ) A Order By OrderBy, FoodIngredient</pre id="code"> Ultimately, this query will be implemented as a web service and consumed by a client application. My question is, is there a way to do what I want with SQL server? Or is it better to just present the query results from the client application (visual c#). Any advice would help me a lot! Cheers, Atzakas

On a quick and first sight…
Where upper(t_Foods.Food_Name) Like ‘%Spaghetti%’ You might want to change this to
Where upper(t_Foods.Food_Name) Like ‘%SPAGHETTI%’ —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

or use Food_Name LIKE ‘%Spaghetti%’ only Madhivanan Failing to plan is Planning to fail

Thank you for the correction. However, this is not what I am asking <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Have a look at the results of my SELECT queries 1) BasilSpaghetti Alla Napolitana ButterSpaghetti Alla Napolitana Chicken BrothSpaghetti Alla Napolitana GarlicSpaghetti Alla Napolitana Green PepperSpaghetti Alla Napolitana Olive OilSpaghetti Alla Napolitana Parmesan cheeseSpaghetti Alla Napolitana ParsleySpaghetti Alla Napolitana Tomato PasteSpaghetti Alla Napolitana BaconSpaghetti Carbonara BasilSpaghetti Carbonara ButterSpaghetti Carbonara CreamSpaghetti Carbonara Green PepperSpaghetti Carbonara HamSpaghetti Carbonara OreganoSpaghetti Carbonara Parmesan cheeseSpaghetti Carbonara ParsleySpaghetti Carbonara SpaghettiSpaghetti Carbonara 2) Spaghetti Alla Napolitana Spaghetti Carbonara Bacon Basil Basil Butter Butter Chicken Broth Cream Garlic Green Pepper Green Pepper Ham Olive Oil Oregano Parmesan cheese Parmesan cheese Parsley Parsley Spaghetti Tomato Paste In case 1) the food name is repeated for every ingredient. In case 2) the food names are listed first and then the ingredients are listed together. I suppose that the first version can be handled by the client application and presented however I want. But there is a lot of information repeated <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />

So you want to have a list in the web application, where you click on the food name, and you get a list of the ingredients? Without a round-trip to the server? In that case, hiding/showing the stuff must be handled by the web application. The UNION query is a good idea, but you’ll have to return three columns to the web application. This also means you have to order by "Food_Name, OrderBy, Ingredient_Name". Your web application can do a simple loop through the recordset and read from that. Add a new main item when there’s a 0 on OrderBy, and enter the food name as the value. Then for each 1 add a subitem, and enter the ingredient as the value – repeat until there’s another 0 on OrderBy or you’re at the bottom of the resultset. SELECT T.* FROM
(Select t_Foods.Food_Name As Food_Name, 0 As OrderBy, ” As Ingredient_Name
From t_Foods
Where Food_Name LIKE ‘%Spaghetti%’
Union All
Select t_Foods.Food_Name As Food_Name, 1 As OrderBy, t_Ingredients.Ingredient_Name As Ingredient_Name
From t_Ingredients
Inner Join Foods_Ingredients
On t_Ingredients.Ingredient_Id = Foods_Ingredients.Ingredient_ID
Inner Join t_Foods
On Foods_Ingredients.Food_ID = t_Foods.Food_ID
Where t_Foods.Food_Name Like ‘%Spaghetti%’) As T
ORDER BY T.Food_Name, T.OrderBy, T.Ingredient_Name

if this is just a low usage db, its ok,
for heavy usage
create a new table, 1:many child of the food table,
keyed on foodID, foodsubID on an insert into food, each word is inserted into the foodsub table with a key back to food

Thank you for your replies. I’ll see what I can do. kind regards
Atzakas

How about this… I could first get the food names only (all the food names that contain the word "pizza", or "spaghetti") and populate a listbox. Then, the user will click on the food name and he will get the ingredients. This will require however 2x calls to the database. The only problem is how to create the "click" event for the listbox. If the listbox contained fixed entries, this would be easy. But now the listbox will contain different entries each time (depending on the query). And one last thing. I have the impression that using datasets does not provide the fast access I would like. What are the alternatives? Is it perhaps better to have a string returned to the application instead of a dataset? Thank you!
Atzakas

Are you planning to make the browser application do all the calls? Not really a ‘hip’ approach these days. You should be building something like an ASP application, where the ASP server handles the interfacing with the db, and just feeds the completed results in HTML format back to the browser application. If you have a limited number of foods and ingredients, you could probably have the whole menu downloaded to the browser client in not too much time. But if you expect the menu to grow, you should probably start with a screen that loads the main sections (antipasti, pastas, pizzas, whatever) including the foods (pizza Alfredo, pizza Quattro Stagioni) – perhaps hiding them in the same technique that I described above – and then have a second call to load the ingredients. With the second call, you could also allow multiple foods to be selected, so there will be one screen with the list of foods and ingredients. So you have to think about program tiers (using ASP as a middle tier) and about the scale (how large will the menu be in two years’ time?).

]]>