Suppose you want to get all the products where category_id = 5. The query that joins the
product and product_category tables is as follows:
SELECT product_category.product_id,
product_category.category_id,
product.name
CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 126
FROM product_category
INNER JOIN product
ON product.product_id = product_category.product_id
ORDER BY product.product_id;
The result will look something like this (to save space, the listing doesn??™t include all
returned rows and columns):
product_id category_id name
------------------------------------------------------------------------------------
1 1 Arc d'Triomphe
2 1 Chartres Cathedral
3 1 Coat of Arms
4 1 Gallic Cock
5 1 Marianne
6 1 Alsace
7 1 Apocalypse Tapestry
8 1 Centaur
9 1 Corsica
10 1 Haute Couture
11 1 Iris
12 1 Lorraine
13 1 Mercury
14 1 County of Nice
15 1 Notre Dame
16 1 Paris Peace Conference
17 1 Sarah Bernhardt
18 1 Hunt
19 2 Italia
20 2 Torch
...
The resultant table is composed of the requested fields from the joined tables synchronized
on the product_id column, which was specified as the column to make the join on. You
can see that the products that exist in multiple categories are listed more than once, once for
each category they belong in, but this problem will go away after we filter the results to get
only the products for a certain category.
Pages:
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221