-- Create catalog_count_products_on_department stored procedure
CREATE PROCEDURE catalog_count_products_on_department(IN inDepartmentId INT)
BEGIN
SELECT DISTINCT COUNT(*) AS products_on_department_count
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
INNER JOIN category c
ON pc.category_id = c.category_id
WHERE (p.display = 2 OR p.display = 3)
AND c.department_id = inDepartmentId;
END$$
The SQL code is almost the same as the one in catalog_get_products_on_department,
which we??™re discussing next.
catalog_get_products_on_department
When the visitor selects a particular department, apart from needing to list its name, description,
and list of categories (you wrote the necessary code for these tasks earlier), you also want
to display the list of featured products for that department.
catalog_get_products_on_department returns all the products that belong to a specific
department and has the display set to 2 (product on department promotion) or 3 (product on
department and catalog promotion).
In catalog_get_products_in_category, you needed to make a table join to find out the
products that belong to a specific category. Now that you need to do this for departments, the
task is a bit more complicated, because you can??™t directly know what products belong to each
department.
CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 134
You know how to find categories that belong to a specific department (you did this in
catalog_get_categories_list), and you know how to get the products that belong to a specific
category (you did that in catalog_get_products_in_category).
Pages:
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232