Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Talal Hibatullah 7Talal Hibatullah 7 

Combining 2 SOQL subqueries

Hi,

I have 3 objects

1. Case
2. Risk Profile
3. Investment option.

Case and Investment option has a look up field to Risk Profile.

I am trying to write a query to include details from the case, related risk profile and all the child investment options to the given risk profile.

So far I am able get the information in 2 separate queries, haven't found much luck in combining them togather.

Query 1. Select name, (SELECT name FROM investment_options__r) From Risk_Profile__c
Query 2. Select name, (SELECT name FROM Cases) From Risk_Profile__c

Any Help is appreciated.

Thanks

Talal

 

Best Answer chosen by Talal Hibatullah 7
SijuSiju
Please try this 
​Select name, (select name from investment_options__r), (select id from cases__r) from Risk_Profile__c

If it's not working ,please check the Child Relationship Name of the custom field and append __r in that.

All Answers

FearNoneFearNone
hi Talal,

you are on the right track. 
select name, (select name from investment_options__r), (select name from Cases) from Risk_Profile__c


hope this will help.
 
Talal Hibatullah 7Talal Hibatullah 7
Thanks FearNone.

I am geeting closer to a solution. Now I am able to combine all the queries. but doesn't seems to like case as child object to a custom object.

The first 2 samples work. 
SELECT Name, (SELECT Id, AccountId, CaseNumber FROM Cases) FROM Account
​Select name, (select name from investment_options__r), (select id from tests__r) from Risk_Profile__c

The third one is not working. I am prompted with an error message.
  
Select name, (select name from investment_options__r), (select id from cases) from Risk_Profile__c

User-added image
 
SijuSiju
Please try this 
​Select name, (select name from investment_options__r), (select id from cases__r) from Risk_Profile__c

If it's not working ,please check the Child Relationship Name of the custom field and append __r in that.
This was selected as the best answer
FearNoneFearNone
Hi Talal,

the next error means that the "Risk_Profile__c" and "Cases" are not related to each other. Add Risk_Profile as Master or Look-up field in Cases-object.
https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/relationships_among_objects.htm
Talal Hibatullah 7Talal Hibatullah 7
Hi FearNone,

Case object definitely has a lookup relationship field to Risk Profile.

1. Case has a lookup field to risk profile
2. investment option also has a lookup field to risk profile

Talal
 
Talal Hibatullah 7Talal Hibatullah 7
User-added image
FearNoneFearNone
i see,
change your code to: 
Select name, (select name from investment_options__r), (select id from cases__r) from Risk_Profile__c
since it is already a query to a custom object, "__r" should now be appended.

 
Talal Hibatullah 7Talal Hibatullah 7
Thanks Siju & FeaNone. It worked. :)