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:
Flour Salad Butter 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?