This is also the first time we??™re using subqueries in this book. In the following code, we
have highlighted the subqueries to make them easier to read. As you can see, a subquery is
just like a simple query, except it??™s written inside another query.
An interesting detail about subqueries is that most of the time they can be used instead of
table joins to achieve the same results. Choosing one solution over the other is, in many cases,
a matter of preference. In mission-critical solutions, depending on the circumstances, you
may choose one over the other for performance reasons. However, this is an advanced subject
that we??™ll let you learn from specialized database books or tutorials.
-- Create catalog_get_product_locations stored procedure
CREATE PROCEDURE catalog_get_product_locations(IN inProductId INT)
BEGIN
SELECT c.category_id, c.name AS category_name, c.department_id,
(SELECT name
FROM department
WHERE department_id = c.department_id) AS department_name
-- Subquery returns the name of the department of the category
FROM category c
WHERE c.category_id IN
(SELECT category_id
FROM product_category
WHERE product_id = inProductId);
-- Subquery returns the category IDs a product belongs to
END$$
Well, that??™s about it. Right now, your data store is ready to hold and process the product
catalog information. To make sure you haven??™t missed creating any of the stored procedures,
you can execute the following command, which shows the stored procedures you currently
have in your database:
SHOW PROCEDURE STATUS
CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 137
By writing the stored procedures, you??™ve already implemented a significant part of your
product catalog! It??™s time to move to the next step: implementing the business tier of the product
catalog.
Pages:
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236