function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sroberts_MEsroberts_ME 

SOQL relationship querys

Hi all,

 

I am trying to get a list of all of the products that a given account owns. I read the page on relationship queries but I can quite figure out how to get this working.

 

public list<account> Accounts = [SELECT Account.Name, (SELECT (SELECT Product__c.Name FROM Asset__c.Product__c) FROM Account.Asset__c) FROM Account];

 

That's the query ive been working on. Any help would be appreciated.

 

Thanks,

-Sam

Shashikant SharmaShashikant Sharma

try this

 

public list<account> Accounts = [SELECT Account.Name, ( SELECT id , (SELECT Product__r.Name FROM Asset__r) FROM Account__r) FROM Account];

 

There is very minimum chance it will work,

 

Please provide me info about yuor data model for these object which is related to which one and

what is

Product__c : it is a llok up field

 

Could you give me all relationship name used in above query.
Bramha1Bramha1

Hi,

 

   What are your Objects and their relationships. Can you let me know the fields as well. 

 

 

Cheers

 

Bramha
MiddhaMiddha

This query will get you all the Assets related to an Account with all the Product information you want:

 

List<Account> accountList = [Select Id,Name, (Select Id, Product2Id, Product2.Name, Product2.ProductCode, Product2.Family From Assets) From Account];
sroberts_MEsroberts_ME

OK, so this is the relationship. The standard account object is the master with Asset__c (custom object) as a detail. The asset then has a look up field that is called product__c that is the ID of the standard Product object associated with that asset. I need the list of accounts with the product information so that I can later weed out accounts that do no own the products I am interested in.

 

thanks,

sam
Bramha1Bramha1

Hi,

 

  [ Select Id , Name, (Select Id, Name , Product__c (Select Id, Name from Product) from Asset__r) from Account ];

 

Please check your relationship names in your org for the ones in red. 

 

Cheers

 

Bramha

 
sroberts_MEsroberts_ME

So I've looked into my wsdl and it seems to me that the relationship from accounts to assets is Assets__r and the relationship from assets to products is Product__r.

 

however, using this query:

 [SELECT Id, Name, (SELECT Id, Name, Product__c, (SELECT Id, Name FROM Product__r) FROM Assets__r) FROM Account];

 

I still get a message that it cant find product__r.
Bramha1Bramha1

Just simple use Product instead od product__r and check if that works. Or even check it with Products__r.

 

Cheers

Bramha
sroberts_MEsroberts_ME

I tried both of those, no luck. Thats the only product object we have though, not sure what else it could be.
Bramha1Bramha1

R u getting error in the query or in other parts of the code? If your using Product__r after the query execution u should include that in the query.

 

 

Cheers

 

Bramha
sroberts_MEsroberts_ME

the error is in the query itself where i put Product__r or Product. Here is the line from the wsdl in the Asset__c object

 

 

element name="Product__c" nillable="true" minOccurs="0" type="tns:ID"/>
element name="Product__r" nillable="true" minOccurs="0" type="ens:Product2"/>
Bramha1Bramha1

Hi,

 

   Just try to follow the naming conventions and correct relationshipnames. Check the below link which can figure out you error.

 

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#CSHID=sforce_api_calls_soql_select.htm|StartTopic=Content%2Fsforce_api_calls_soql_select.htm|SkinName=webhelp

 

 

 

Cheers

 

Bramha

 

 

 
Bramha1Bramha1

Hi,

 

Just try the below:

 

  Select Id, Name , Asset__c, Asset__r.Product__r.Id, Asset__r.Product__r.Name from Account 

 

Try the red with Assets__r as well 

 

 

Cheers

 

Bramha