SQL Server query WHERE with JOIN

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

What's wrong with adding `WHERE AsianRecipe.NAME LIKE '%MyName%` at the end? — zimdanen, May 02 '13 at 20:06
`LIKE` or `PATINDEX`? Or perhaps see: http://stackoverflow.com/questions/194652/sql-server-regular-expressions-in-t-sql — Orbling, May 02 '13 at 20:06
@NickVaccaro you may be making a joke, but it is a little scary to think there are separate tables for different cuisines. — Tim Lehner, May 02 '13 at 20:52
@NickVaccaro, not a joke there are tables for different cuisines. Each cuisine also has different categories. And each recipe in each cuisine has rating(s), i.e., Asian recipe 1 can be rated, 1 to 5. The ratings are then counted and an average is rendered. — user1830924, May 02 '13 at 21:11
I know this goes beyond the scope of this question, which is why I only commented on it. If you would like to talk about the pros and cons of this design, I can make myself available. This has some pretty bad code smell. — Nick Vaccaro, May 02 '13 at 21:14
@NickVaccaro, thank you very much Nick. For you pros, it is overly evident that I don't know what I'm doing. Any help would be greatly appreciated. — user1830924, May 02 '13 at 21:43
@user1830924 it sounds like there could be a `Cuisines` table and a `Categories` table, then, and change `Asian...` tables to `Recipes` and `Ratings`. — Tim Lehner, May 02 '13 at 22:04
@TimLehner, on HOME page there is a dropdown which lists Cuisines, i.e., African, Asian, etc. Selecting African goes to a search for African db of four tables - recipes, ratings, directions and the fourth, ratingAVGs (which is a COUNT and AVG of ratings.) What you gave me today - I enter 'snow peas' and all recipes - could be many - for snow peas comes up along with each recipe's rating in stars, along with the number of ratings plus a small description of the recipe. The user then clicks on the recipe of choice and goes to the ingredients and directions for that particular recipe. ??? — user1830924, May 02 '13 at 22:16
Created chat room (http://chat.stackoverflow.com/rooms/29367/db-schema-chat) but you might need 20 rep to get in. Lemme know. — Nick Vaccaro, May 03 '13 at 12:51
@Nick Vaccaro, I can see the chat room but there appears no way for me to enter anything. It does say I'm 13. — user1830924, May 04 '13 at 17:26

score 2 · Answer 1 · answered May 02 '13 at 20:17

2

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

answered May 02 '13 at 20:17

RichardTheKiwi

105,798
26
196
262

There is a total of 3 tables. Two can have data entered. The third is derived from a query made only on one of the other two tables. If I understand your comment, the only derived table is AsianRatingAVG – user1830924 May 02 '13 at 21:15

Tim Lehner · Accepted Answer · 2013-05-02T20:50:47.287

2

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.

edited May 02 '13 at 20:50

answered May 02 '13 at 20:17

Tim Lehner

14,813
4
59
76

As mentioned by someone else, that could be due the the fact that you're using an Int32 rather than a string in your parameter. – Tim Lehner May 02 '13 at 21:38
you cannot believe how much I appreciate the help everyone on here gave me, especially your help - right on the spot. – user1830924 May 02 '13 at 21:39
one more bother is you don't mind. Everything you told me works great plus I expanded to include category in the search. Is there a tweak to the WHERE you gave me so that a misspelled word, for example, snaw peas would render snow peas? Thanks – user1830924 May 07 '13 at 21:34
I have used your code and it works great in a Web Project. I started finalizing everything in a Web Site, and got to using your code, and it will not work in a Site - it keeps saying I must declare a scalar value for Name. I've tried everything. Can you give me a hint on what I need to do? – user1830924 Aug 29 '13 at 18:58
You might want to put this into a new question with the exact code and error. This probably has to do with "@Name" and sending a parameter named that along with your SQL query. – Tim Lehner Aug 29 '13 at 21:26

jerrylagrou · Answer 3 · 2013-05-02T20:19:26.353

Even though you wrote Int32, I'll presume that you meant for that form entry field to be a string.

select *
from 
(
[Your 'Original' Query]
) X
WHERE Name like '%'+@Name+'%'

You used 'RecipeID' twice in your original query, so you will need to tweak that, but otherwise you are ok.

or

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
WHERE AsianRecipe.NAME like '%'+@Name+'%'  -- ADDED A NEW LINE HERE!!!
) AS AsianRecipe ON (AsianRatingAVG.RecipeID = AsianRecipe.RecipeID)

!!!NOTE!!! THIS WILL NOT SCALE WELL!!!

But if your db only has a thousand or so recipe's you'll be ok.

If you have 1000's of recipes, or want to be able to work with millions, you will have to use full text searches. That is a whole different situation than tweaking a query.

SQL Server query WHERE with JOIN

3 Answers3