SQL Server query WHERE with JOIN
I want to do a SELECT which will result in a pseudo-search of a database. The following SELECT is used in VS2010 with SQL Server:
SELECT * FROM ( SELECT RecipeID, COUNT(*) AS Count, AVG(Rating) AS RatingAVG FROM AsianRating GROUP BY RecipeID ) AS AsianRatingAVG INNER JOIN ( SELECT AsianRecipe.RecipeID, AsianRecipe.Category , AsianRecipe.NAME, AsianRecipe.Description FROM AsianRecipe ) AS AsianRecipe ON (AsianRatingAVG.RecipeID = AsianRecipe.RecipeID)
If the JOIN was not used and only the AsianRecipe table is used, I can do the search for a recipe name. The three tables use RecipeID (int) as keys. Name is not common among the three tables. Without the JOIN, using a SEARCH textbox, the following will give a pseudo-search if used with a WHERE:
<SelectParameters> <asp:FormParameter FormField="Name" Name="Name" Type="Int32" /> </SelectParameters>
Is there any way to write a WHERE using the above query so the search is done on the name of recipes?
Is there any way to do the search if only a partial name is entered? Any help will be greatly appreciated!
I'm not sure if this is faster than joining your derived tables, but I like the looks of it better:
SELECT re.RecipeID , re.Category , re.NAME , re.Description , count(ra.RecipeID) AS [Count] , avg(ra.Rating) AS RatingAVG FROM AsianRecipe re LEFT JOIN AsianRating ra ON re.RecipeID = ra.RecipeID WHERE re.NAME LIKE '%' + @Name + '%' -- Your partial match search GROUP BY re.RecipeID , re.Category , re.NAME , re.Description
You can remove the '%' for an exact match.
Note that this will also get recipes that have no ratings. Change LEFT JOIN to JOIN if that's not correct.
Also, you might need to break out multiple search terms if someone puts multiple words in your search box. A quick and dirty way to do it would be replace(@Name, ' ', '%') but that would only find the search terms in the same order.
Using a derived table (or CTE) to create the aggregates is the fastest method on my box. Next would be correlated subqueries and finally my original query. Very informal testing...YMMV.
As such, I'm upvoting @RichardTheKiwi. My other points still stand.
It would have been much easier if you didn't make two derived tables when only one will do (for the pre-aggregate).
SELECT rep.RecipeID, rep.Category, rep.NAME, rep.[Description], rat.[Count], rat.RatingAVG FROM ( -- pre-aggregated SELECT RecipeID, COUNT(*) AS Count, AVG(Rating) AS RatingAVG FROM AsianRating GROUP BY RecipeID ) AS rat JOIN AsianRecipe rep ON rat.RecipeID = rep.RecipeID; -- WHERE rep.name LIKE '%partial%'