Product catalog query that returns master products where criteria applied to child columns
Posted on 2009-03-30
I have 3 tables as follows:
content is the central content table and represents various content, content_products is an extension table, i.e. it stores details such as product_type, product_price, product_category to extend content. the combination of content and content_products joined on content_id is a product.
There are 2 types of product_type, these are Master and Variant. A Master product stores product_category but does not store product_price. A Varient product does not store product_category but does store price.
The relationship between a master and its variants is mapped by the table content_associations using from_content_id and to_content_id.
So a single product will have 1 Master product entry and as many Variant product entries as appropriate. They are then linked.
I am in need of a query that can return all Master product rows where a criteria is applied to the set of associated Variants.
For example "find all Master products that have child Variant products with a price > 10".
The query should return all columns from content and content_products for the Master only where the variant conditions hold true.