- Okay, Back to Business
- Match the Subcategory
- Selecting the Final Component
Match the Subcategory
32 /* Now, pull the matching subcategory 33 with parent ID matching above */ 34 SELECT TOP 1 35 @nid2 = n.navigation_id 36 FROM 37 products p, 38 product_tax_mapping ptm, 39 reference_dimension rd, 40 navigation n 41 WHERE 42 p.product_id=@prodid 43 and ptm.product_id = p.product_id 44 and ptm.reference_id = rd.reference_id 45 and n.facet_column='subcategory' 46 and rd.subcategory = n.facet_column_value 47 and n.parent_navigation_id = @nid 48 ORDER BY 49 n.facet_column_value ASC
Above we have essentially the same algorithm, with the subtle difference that we want to match on the second level of the reference_dimension, the subcategory. This introduces the obvious alterations seen on lines 45 and 46. subcategory has replaced category. We also add the extra wrinkle that the previously determined navigation_id must be listed as the parent_navigation_id for the current record. There is a java subcategory under the programming category. There is also a java subcategory under web development. If we pulled the programming category, we want to make sure that we pull the top subcategory from the proper place.
One more difference can be seen. This time we have ordered by facet_column_value, the name of the subcategory. Our editors are not quite intrepid enough to be expected to assign sibling priorities to the vast number of subcategories in the database. So we do it alphabetically here. I suppose that the editorial staff must have something else to do.
50 /* Finally, pull the tab_navigation_id corresponding 51 to the productID */ 52 IF @nid2 is NULL 53 BEGIN 54 SELECT @nid2 = @nid 55 END
Why perform this check in lines 52–55? A product is not required to be tagged to the subcategory level. If no subcategory navigation_id could be found, we'll just return the category navigation_id. If that was null too, we return a null. That naughty editorial staff forgot to tag this product. We handle that case in State.asp.