By combining these pieces
of information, you can generate the list of products in a department. For this, you need two
table joins.
You will also use the DISTINCT clause to filter the results to avoid getting the same record
multiple times. This can happen when a product belongs to more than one category, and
these categories are in the same department. In this situation, you would get the same product
returned for each of the matching categories, unless the results are filtered using DISTINCT.
-- Create catalog_get_products_on_department stored procedure
CREATE PROCEDURE catalog_get_products_on_department(
IN inDepartmentId INT, IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
PREPARE statement FROM
"SELECT DISTINCT p.product_id, p.name,
IF(LENGTH(p.description) <= ?,
p.description,
CONCAT(LEFT(p.description, ?),
'...')) AS description,
p.price, p.discounted_price, p.thumbnail
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 = ?
ORDER BY p.display DESC
LIMIT ?, ?";
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inDepartmentId;
SET @p4 = inStartItem;
SET @p5 = inProductsPerPage;
EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;
END$$
?– Tip If the way table joins work looks too complicated, try following them on the diagram shown earlier in
Figure 5-6.
Pages:
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233