Table 2.4. Our ingredients Table
ItemID
apple1
banana2
kiwifruit3
strawberries4
flour5
fruit juice6
butter7
sugar8
Table 2.5. Our recipe_ingredients table
Ingredient_idRecipe_id
11
71
81
51
62
22
12
32
42
These tables are hardly readable, but they represent the correct way of showing this
data. As soon as we join the tables together, well easily be able to gain a perspective
of the whole picture.
Inner Joins
To join over a linking table, well need to start at the recipes table, make a join to
the recipe_ingredients table, and then link from there to the ingredients table. Heres
the SQL well use to do this:
65Databases
SELECT recipes.name, ingredients.item
FROM recipes
INNER JOIN recipe_ingredients
ON recipes.id = recipe_ingredients.recipe_id
INNER JOIN ingredients
ON recipe_ingredients.ingredient_id = ingredients.id;
This SQL only selects the two columns we ask for, so we need never be concerned
about the numeric identifiers that are used inside the database to make the relation-
ships work correctly. This query will output the following data set seen in Table 2.6.
Table 2.6. Data output from a JOIN statement
ItemName
appleApple Crumble
flourApple Crumble
butterApple Crumble
sugarApple Crumble
fruit juiceFruit Salad
bananaFruit Salad
appleFruit Salad
kiwifruitFruit Salad
strawberriesFruit Salad
This is an example of an inner join, which means we only see data where there are
matching rows in all the tables in the query. We have other entries in the recipes
table, but since were yet to link any ingredients to them, they dont appear here.
To see all the recipes, with or without ingredients, well use an outer join.
Join = Inner Join
Youll sometimes see queries that just use the JOIN keyword on its own; these
are implicit inner joins. This example uses the INNER keyword to make it clearer
what is happening. Well go on to look at other join types shortly.
PHP Master: Write Cutting-edge Code66

Get PHP Master now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.