I'm having trouble conceptualizing how to query a set of tables that have various granularities of data, to get the information that I want. Let me explain the general table structure:

Table 1: Recipes - this consists of two columns, that can be thought of as parent and child. Two such relationships might be this set of rows:

Recipe      Ingredients

Salad       Lettuce
Salad       Dressing
Salad       Cucumbers
Salad       Chicken
Pasta       Spaghetti
Pasta       Tomato Sauce
Pasta       Chicken

As you can see, there might be "children" shared across multiple parents (in the example above chicken is in both my made up salad, and my made up pasta), but the full set of children uniquely defines a single parent (if you have the grouping of lettuce, dressing, cucumbers, and chicken, it is always salad - there's no ambiguity).

On the other hand, let's say I have a table that has a list of ingredients (IngredientList), some of which are just by themselves, and some of which are part of a recipe. I can identify how things are grouped. Conceptualize this information as the following example

Ingredient     IngredientGroup

Flour          1
Lettuce        2
Cucumbers      2
Dressing       2
Chicken        2
Butter         3
Ground Beef    4

The output I want collapse each ingredient group into either itself (if it isn't part of a recipe) or the recipe (if the ingredients are part of a recipe). Thus, the output I want is:

Ground Beef

If ingredients did NOT share children, I know I could do something like this:

select coalesce(recipes.recipe, ingredientlist.ingredient)
from ingredientlist
left join recipes on recipes.ingredient = ingredientlist.ingredient
group by coalesce(recipes.recipe, ingredientlist.ingredient)

except, I know I will join chicken to both salad and pasta, thus getting "pasta" in my output - which I don't want. I need some way to uniquely tie the set of ingredients (which is across four rows, and grouped together using the "IngredientGroup") to the recipe that I want. Is there a way to do this effectively?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.