none
WHERE with JOIN RRS feed

  • Question

  • 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
          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!


    Earl Davis

    Thursday, May 2, 2013 7:53 PM

Answers

  • What about modifying of this part

     JOIN (SELECT AsianRecipe.RecipeID, AsianRecipe.Category, AsianRecipe.Name, AsianRecipe.Description
          FROM AsianRecipe
    WHERE Name = @NameParameter)


    Please Mark as Answer and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.me/


    Saturday, May 4, 2013 9:55 PM