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!

Answers


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.

UPDATE

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%'

Need Your Help

301 htaccess redirection from old permalinks to new permalinks

wordpress .htaccess url-redirection permalinks

I recently transferred my Blogger blog to WordPress, and I want to redirect old Blogger permalinks to the new site.

When calling a static method of class A from class B, does the main method of class A run?

java main

I have a class Dictionary. It just reads a text file (from the main method) containing some words and stores them in a hash set. The class also has a method "contains"(static) that checks if the Ha...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.