function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Puneeth KumarPuneeth Kumar 

SOQL query to fetch product name and product group name.

Hi All,

I want to get the the product name(in Product2 object), product group name (in Product_Hierarchy__c) from price book entry on below criteria, I have written this query which is wrong I believe. Can anyone please help me out in querying. Providing relationship btween objects from schema builder.
User-added image

Select Product2.Name,
Product_Hierarchy__c.Name
From PricebookEntry, Product2, Product_Hierarchy__c
Where PricebookEntry.Pricebook2Id = ’ 01s30000000GUqhAAG’ and
PricebookEntry.Product2Id = Product2.Id and Product2.Product_Hierarchy__c = Product_Hierarchy__c.Id

Please advice.
Anupama SamantroyAnupama Samantroy
Hi Puneeth,

You need to get the Product Id and the Product Hierarchy id from somewhere or you cn hardcode like you did for the Pricebook. Hardcoded is not recommended though in code but if you just want to run this query for specific data then its fine.
Id prodId = //fetch the product id and assign
Id prodHierarchyId = //fetch the Product Hierarchy id

Select Product2.Name, Product_Hierarchy__c.Name
From PricebookEntry
Where PricebookEntry.Pricebook2Id = ’01s30000000GUqhAAG’ AND
PricebookEntry.Product2Id =: prodId AND
Product2.Product_Hierarchy__c =: prodHierarchyId

Thanks
Anupama
Ishwar ShindeIshwar Shinde
What is a relationship between product and product hirarchy? Who is a parent here?
Puneeth KumarPuneeth Kumar
Hi Anupama,

Thanks for your reply. I want the query on specific Price book so I can fetch all the products name and products groups in my API. Just need to fetch a specific price book details evrytime. What might be your suggestion. Thank you.

Hi, Ishwar, from my schema I can see that as a lookup relationship.
Anupama SamantroyAnupama Samantroy
You can store the Id of the Pricebook in one custom label and use that in your code. In this way you can just change the custom label when moving using in different sandboxes and Production.

If you just need the list of Product Names and the Product Group names then use the query below. I have removed the filter on Product and Product Group as you need all products.
//store id of pricebook in custom label
//01s30000000GUqhAAG
//PriceBookId -- name of custom label
Select Product2.Name, Product_Hierarchy__c.Name
From PricebookEntry
Where PricebookEntry.Pricebook2Id =: System.label.PriceBookId

Thanks
Anupama