Now the objective of my question is to retrieve Product profile(product) for a given set of characteristics Value (multiple characteristics values). e.g. What product should I use if I need Color as Black and Material Type as Solid. Where Color and Material Type are Characteristics and Black & Solid are Characteristics Values.
So I was looking around some SOQL / SOQL queries to resolve this issue, but so far not able to get the desired output. I have also attached sample table with some data.
Here are some of the SOQL queries I have tried so far :-
SELECT Product_Characteristic__c.Product_Profile__c FROM Product_Characteristic__c WHERE Product_Characteristic__c.Value_Reference__r.NAME LIKE '%Black%' OR Product_Characteristic__c.Value_Reference__r.NAME LIKE '%Solid%' GROUP BY Product_Characteristic__c.Product_Profile__c SELECT Id,Name FROM Product_Profile__c WHERE Id IN (SELECT Product_Profile__c FROM Product_Characteristic__c WHERE Product_Characteristic__c.Value_Reference__r.Name like '%Black%' OR Product_Characteristic__c.Value_Reference__r.Name like '%Solid%')
I would like to use AND instead of OR but it fetches no results. And I know that there is a product which has both Black and Solid as its characteristics value (product1 - see image above)