Flexible query to filter product based on classification attribute value

[former_member686625]

Hi,

I would like to filter out product having value "Bagged - Ground" in classification attribute "PackType".
I tried to user ProductFeature and able to filter out product having classification attribute Pack Type but facing issue as of now to put check on its value that is "Bagged - Ground"

Can anyone help?

Thanks,

Manish Yadav

[former_member686625]

Solution:

SELECT {p.code},{cav.name},{se.code} FROM {Product! as p JOIN ProductFeature as pf ON {pf.product}={p.pk} JOIN ClassificationAttributeValue as cav ON {pf.stringValue}={cav.pk} JOIN ArticleApprovalStatus as s on {p.approvalStatus}={s.pk} JOIN SeriesEnum as se on {p.series}={se.pk} JOIN CatalogVersion as cv ON {cv.pk}={p.catalogVersion} JOIN Catalog as cat ON {cat.pk}={cv.catalog}} WHERE {cat.id}='<CATALOG_CODE>' AND {cv.version} LIKE 'Online' AND {pf.qualifier}='cab2cClassification/1.0/coffee.packagetype' AND {cav.name} IN ('Bagged – Ground','Bagged – Whole Beans') AND {s.code}='approved' AND {p.customerServiceOnly}=0