Problem
Retrieve color of all kitchen appliances along with their name.
Assumption
1. There is catentry type for Kitchen Appliances and all kitchen appliances are mapped with this type.
2. Color has been stored as an attribute.
Tables Referenced
catenttype -> catentry -> attr -> attrdesc -> catentryattr -> attrval -> attrvaldesc
1. catenttype -> Catalog Entry Type Master table
2. catentry -> Catalog Entry Table
3. attr -> Attribute
4. attrdesc -> Attribute Description
5. catentryattr -> Relationship between catentry, attribute and attribute value.
6. attrval -> Attribute Value
7. attrvaldesc -> Attribute Value Desciption
Solution
1. Get catentry type id for "Kitchen Appliances"
select catenttype_id from catenttype where description = 'Kitchen Applicances';
2. Get catentries belonging to this catentry type
select catentry_id from catentry where catenttype_id = <Retrieved above>
3. Get Attr id for attribute color
select attr_id from attrdesc where Name='Color'
4. Get attr value ids for color attribute for these catentries Ids
select attrval_id from catentryattr where catentry_id in ( <Retrieved in 2 >) and attr_id=<Retrieved in 3>
5. Get attr value name for these attrval ids
Select value from attrvaldesc where attrval_id in < Retrieved in 5 >
