5

I have a table Product2, witch contain products. I have a table PricebookEntry, witch contain price for products.

How can i make a request for someting like :

SELECT Id, desriprtion, title, [...]
FROM Product2
INNER JOIN PricebookEntry ON PricebookEntry.Product2Id=Product2.Id
WHERE Product2.reference LIKE '%MyProductRef%'

I need to select the products info and their price but they are not in the same table.

Can someone help me please ?

4
  • 2
    Take a look at this topic: A Deeper look at SOQL and Relationship Queries on Force.com and scroll down to the SOQL Joins Dec 23, 2014 at 14:31
  • Okay, i solved the problem by ckecking "Child Relationships" on the Force.com IDE. I have all the fields i need, but how can i display them on my visualforce page ? I make a apex:repeat, but how can i call the filed of the child records ?
    – CotralLab
    Dec 23, 2014 at 15:32
  • 1
    You need to access the child relationship name like a list: <apex:repeat value="{!Product2.ChildRelationshipName__r}" var="child">{!child.UnitPrice} Dec 23, 2014 at 15:35
  • @mast0r Yes it works ! Thank you :-)
    – CotralLab
    Dec 23, 2014 at 15:44

1 Answer 1

8

Try this :

SELECT Id, description, title, [...] 
FROM Product2 
WHERE Id in (SELECT Product2Id FROM PricebookEntry) AND reference LIKE '%MyProductRef%');
3
  • Yes, this works, but i can't select fields from PricebookEntry. For exemple, i need to select the field PricebookEntry.UnitPrice
    – CotralLab
    Dec 23, 2014 at 14:23
  • 1
    @CotralLab You can select these fields through the relation ship access, something like: SELECT Id, description, title, (Select UnitPrice From PricebookEntryRelationShipName__r) From Product2 ... Dec 23, 2014 at 14:38
  • 3
    There are no 'joins' (syntax-wise) in SOQL, so the link above from mast0r is your best friend to get the return you need using the Relationship syntax.
    – HomerJ
    Dec 23, 2014 at 15:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .