`product_id`, `av`.`attribute_value_id`
FROM `product` `p`, `attribute_value` `av`;
4. Execute the following SQL code, which creates the catalog_get_product_attributes stored procedure.
This stored procedure will associate the attributes assigned to a product. Don??™t forget to set the
delimiter to $$ before executing the code .
-- Create catalog_get_product_attributes stored procedure
CREATE PROCEDURE catalog_get_product_attributes(IN inProductId INT)
BEGIN
SELECT a.name AS attribute_name,
av.attribute_value_id, av.value AS attribute_value
FROM attribute_value av
INNER JOIN attribute a
ON av.attribute_id = a.attribute_id
WHERE av.attribute_value_id IN
(SELECT attribute_value_id
FROM product_attribute
WHERE product_id = inProductId)
ORDER BY a.name;
END$$
How It Works: Data Logic for Product Attributes
Here, we??™ll discuss the code used to populate the product_attribute table and the catalog_get_product_
attributes stored procedure. Before proceeding, please make sure you understand the purpose of each field of
the attribute and attribute_value tables and the code used to populate these tables. It may take a while to
understand the table structure exactly.We will not reiterate the theory here, but feel free to refer to Chapters 4 and 5,
where we??™ve discussed in detail the major concepts of relational database design.
CHAPTER 6 ?– PRODUCT ATTRIBUTES 178
When populating product_attribute, the goal is to associate all the existing attribute values (via the attribute_
value_id field) to each of our products (via the product_id field).
Pages:
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279