SQL query for classification Attributes and their value for a product

[Former Member]

Can somebody help me with the SQL query to fetch classification Attributes and their values for a product from the underlying data model ? I have Used the following query but the results seems to be duplicated :

 SELECT p.pk product_id,
        p.p_code product_code,
        ca.P_CODE classification_attribute,
        ca.pk class_atribute_id,
        a2v.P_VALUE value_id,
        cav.p_code value,
        p.p_baseproduct
 FROM productfeatures pf
 LEFT OUTER JOIN products p
 ON pf.p_product = p.pk
 LEFT OUTER JOIN cat2attrrel c2a
 ON c2a.pk = pf.p_classificationattributeassig
 LEFT OUTER JOIN classificationattrs ca
 ON c2a.p_classificationattribute = ca.pk
 --- not very sure of this part
 LEFT OUTER JOIN ATTR2VALUEREL  a2v
 --ON a2v.P_ATTRIBUTE = ca.pk
 ON a2v.P_ATTRIBUTEASSIGNMENT = c2a.pk
 LEFT OUTER JOIN CLASSATTRVALUES cav
 ON a2v.P_VALUE = cav.pk
 WHERE  1 = 1
 AND p.pk = 8796093972481;
 

[Former Member]

any pointer?

[Former Member]

Hello,

If you have the product pk, Why do you do join of "all" products? productfeatures table has the product pk, you don't need that join.

I recommend you to do again the query in flexibleSearch and with product code and catalogVersion.