Querying multiple tables

Answer 1

I'll leave it to you to figure out how to format it properly, but to get started you'll need to join the ingredients and recipes tables together, but search the categories (joined as well).

I find the easiest/best way to do the latter is using an IN clause (EXISTS works too). For example:

SELECT *
FROM recipes
LEFT OUTER JOIN recipe_ingredients ON recipe.recipe_id = recipe_ingredients.recipe_id
WHERE recipes.id IN (SELECT recipe_categories.recipe_id
                     FROM categories
                     INNER JOIN recipe_categories ON categories.cid = recipe_categories.category_id
                     WHERE categories.category_name = @search_term)

Note that unless you're searching only one category, doing a straight JOIN will produce multiple rows when a recipe contains more than one matched category, so IN/EXISTS is best.

Answer 2

I've found that you can make sense of things by breaking them down, one step at a time.

First, let's grab our desserts.

SELECT categories.cid
  FROM categories
 WHERE category_name = 'desserts'

Now let's grab all the recipes that are deserts by gluing them on the left side of our results. For every matching row in categories, there can be one or more matching rows in recipe_categories. For every matching row in recipe_categories, there can be one or more matching rows in recipes.

    SELECT categories.cid, recipes.recipe_name
      FROM categories
 LEFT JOIN recipe_categories ON (recipe_categories.category_id = categories.cid)
 LEFT JOIN recipes ON (recipes.rid = recipe_categories.recipe_id)
     WHERE category_name = 'desserts'

Finally, let's attach our ingredients on the left. For every matching row we have so far (we're on recipes now), we can have one or more matches in recipe_ingredients, and then again one or more matches in recipes.

    SELECT recipes.recipe_name, ingredients.ingredient_name
      FROM categories
 LEFT JOIN recipe_categories ON (recipe_categories.category_id = categories.cid)
 LEFT JOIN recipes ON (recipes.rid = recipe_categories.recipe_id)
 LEFT JOIN recipe_ingredients ON (recipe_ingredients.recipe_id = recipes.rid)
 LEFT JOIN ingredients ON (ingredients.iid = recipe_ingredients.ingredient_id)
     WHERE category_name = 'desserts'

With me so far?

Now, it's late, and I haven't tested this, but it should return the data you're looking for. In fact, I may totally be using the wrong join type to get this done correctly. I'm sure someone in the comments will correct me if I'm wrong.

What this won't do is return the data formatted as you'd like. Let's make one last iteration of the query to add some id fields.

    SELECT recipes.rid, recipes.recipe_name, ingredients.iid, ingredients.ingredient_name
      FROM categories
 LEFT JOIN recipe_categories ON (recipe_categories.category_id = categories.cid)
 LEFT JOIN recipes ON (recipes.rid = recipe_categories.recipe_id)
 LEFT JOIN recipe_ingredients ON (recipe_ingredients.recipe_id = recipes.rid)
 LEFT JOIN ingredients ON (ingredients.iid = recipe_ingredients.ingredient_id)
     WHERE category_name = 'desserts'

Let's gather the data into a multi-dimensional array. Let's pretend we're using PDO.

$query = '...';
$sh = $db->prepare($query);
$sh->execute();
$recipes = array();
while($row = $sh->fetch(PDO::FETCH_ASSOC)) {
// For each row in the result set, check to see if we've looked at this recipe.
    if(!array_key_exists( $row['rid'], $recipes )) {
    // If we haven't, let's initialize the row with the recipe name
    // and a place to stick each ingredient.
        $recipes[ $row['rid'] ] = array( 
            'recipe_name' => $row['recipe_name'],  
            'ingredients' => array()
        );
    }
// Place this ingredient with the proper recipe.
    $recipes[ $row['rid'] ]['ingredients'][ $row['iid'] ] = $row['ingredient_name'];
}

The result should be something like:

array(
    1 => array(
        'recipe_name' => 'black forest cake',
        'ingredients' => array(
            '1' => 'self-raising flour',
            '2' => 'milk',
            ...
        )
    )
)

From that structure, you can do what you want.

Answer 3

Query:

SELECT A.recipe_name, GROUP_CONCAT(ingredient_name) AS ingredient_names
FROM recipes A
LEFT JOIN recipe_ingredients B ON A.id = B.recipe_id
LEFT JOIN ingredients C ON B.ingredient_id = C.iid
LEFT JOIN recipe_categories D ON A.id = D.recipe_id
LEFT JOIN categories E ON D.category_id = E.cid
WHERE category_id = <serach_id>
GROUP BY id

Result:

+-------------------+-------------------------------------------------+
| recipe_name       | ingredient_names                                |
+-------------------+-------------------------------------------------+
| black forest cake | chocolate,baking powder,self-raising flour,milk |
| angel cake        | self-raising flour,milk,chocolate               |
| melting moments   | milk,plain flour                                |
| croquembouche     | self-raising flour,plain flour                  |
| crepes suzette    | milk                                            |
+-------------------+-------------------------------------------------+

Here is what you asked for using the GROUP_CONCAT. Each ingredient is seperated by ,