tsql - Ranking a record based on sort order of multiple related records in T-SQL? -

i have 2 tables, plus matching table. argument's sake, let's call them recipes , ingredients. each recipe should have @ least 1 ingredient, may have many. each ingredient can used in many recipes.

 recipes           ingredients       match  ===============   ===============   ===============  id   int          id   int           recipeid     int  name varchar      name varchar       ingredientid int 

sample data:

 recipes           ingredients       match (shown cdl stored above)  ===============   ===============   ===============  soup              chicken           soup:        chicken, tomatoes  pizza             tomatoes          pizza:       cheese, chicken, tomatoes  chicken sandwich  cheese            c. sandwich: bread, chicken, tomatoes  turkey sandwich   bread             t. sandwich: bread, cheese, tomatoes, turkey                            turkey 

here's problem: need sort recipes based on name(s) of ingredients. given above sample data, need sort order recipes:

 turkey sandwich    (first ingredient bread, cheese)  chicken sandwich   (first ingredient bread, chicken)  pizza              (first ingredient cheese)  soup               (first ingredient chicken) 

ranking recipes first ingredient straightforward:

 recipesranked (     select recipes.id, recipes.name, recipes.description,         row_number() on (order ingredients.name) sortorder             recipes         left join match on match.recipeid = recipes.id         left join ingredients on ingredients.id = match.ingredientid     )  select   id, name, description, min(sortorder)      recipesranked  group id, name, description;  

beyond that, i'm stuck. in example above, works, leaves 2 sandwiches in ambiguous order.

i have feeling min(sortorder) should replaced else, maybe correlated subquery looking non-existence of record in same cte, haven't figured out details.

any ideas?

(it possible recipe have no ingredients. don't care order come out in, end ideal. not main concern @ point.)

i'm using sql server 2008 r2.

update: added sql fiddle , updated example here match:

http://sqlfiddle.com/#!3/38258/2

update: have sneaking suspicion if there solution, involves cross-join compare every combination of recipe/ingredient against every other, filtering somehow.

i think give want (based on supplied fiddle)

-- show recipes ranked ingredients alphabetically  recipesranked (     select recipes.id, recipes.name, sortedingredients.sortorder             recipes         left join match on match.recipeid = recipes.id         left join         (           select id, name, power(2.0, row_number() on (order name desc)) sortorder            ingredients) sortedingredients          on sortedingredients.id = match.ingredientid     )  select   id, name, sum(sortorder)      recipesranked  group id, name  -- sort sum of ingredients. since first ingredient both kinds   -- of sandwiches bread, gives both of them same sort order,  -- need turkey sandwiches come out first between them because cheese  -- it's #2 sorted ingredient, chicken #2 ingredient  -- chicken sandwiches.  order sum(sortorder) desc; 

it uses power ensure significant ingredients weighted first.

this work number of recipes , 120 ingredients (in total)

will not work if recipes contain duplicate ingredients, though filter out if occur