SQL Server query WHERE with JOIN -

i want select result in pseudo-search of database. following select used in vs2010 sql server:

select * (    select recipeid, count(*) count, avg(rating) ratingavg    asianrating    group recipeid ) asianratingavg inner join (    select asianrecipe.recipeid, asianrecipe.category       , asianrecipe.name, asianrecipe.description    asianrecipe ) asianrecipe on (asianratingavg.recipeid = asianrecipe.recipeid) 

if join not used , asianrecipe table used, can search recipe name. 3 tables use recipeid (int) keys. name not common among 3 tables. without join, using search textbox, following give pseudo-search if used where:

<selectparameters>    <asp:formparameter formfield="name" name="name" type="int32" /> </selectparameters> 

is there way write using above query search done on name of recipes?

is there way search if partial name entered? appreciated!

i'm not sure if faster joining derived tables, looks of better:

select re.recipeid     , re.category     , re.name     , re.description     , count(ra.recipeid) [count]     , avg(ra.rating) ratingavg asianrecipe re     left join asianrating ra on re.recipeid = ra.recipeid re.name '%' + @name + '%' -- partial match search group re.recipeid     , re.category     , re.name     , re.description 

you can remove '%' exact match.

note recipes have no ratings. change left join join if that's not correct.

also, might need break out multiple search terms if puts multiple words in search box. quick , dirty way replace(@name, ' ', '%') find search terms in same order.

update

using derived table (or cte) create aggregates fastest method on box. next correlated subqueries , original query. informal testing...ymmv.

as such, i'm upvoting @richardthekiwi. other points still stand.