How to use soql queries on multiple look up relationship object to retrieve specific data

Question

I have 4 custom objects (as shown in the schema diagram). And they are associated using a lookup relationship.

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)

Answer 1

You need to first fetch Ids of the Characteristics for Color and Material type. Then you need to fetch ids of the Characteristic values for Black and Solid. Then query the Product characteristic which has these Ids.

Answer 2

I came up with this:

SELECT product_profile__r.name, count(name) from product_characteristic__c WHERE (characteristic__r.name = 'Color' and characteristic_value__r.name = 'Black') OR (characteristic__r.name = 'Material Type' and characteristic_value__r.name = 'Solid') group by product_profile__r.name