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
steven75steven75 

SOQL - query join multiple level relationship

Hi,

 

I am spending alot of time to figure out how to construct the query to get the result I want in SafesForce, but no luck. 

What I have it

1. From Account, I created an Opportunity.

2. From Opportunity, I created a Case.

3. From Case, I create mutilep Tasks associated with the case.

 

The result I try to escomblish is listing these fields:

Account, Opportunity, Case, and Tasks.

Account#1, Opt# 1, Case# 1, Task#1

Account#1, Opt# 1, Case# 1, Task#2

Account#1, Opt# 1, Case# 1, Task#3

Account#2, Opt# 2, Case# 2, Task#4

Account#2, Opt# 2, Case# 2, Task#5

 

I have put together this query, but it only show Account.Name, Opportunity, and Case,  Tasks are not show up since they are not tied to Case (if I created Task from Account, then it will show up in this query, but I need to create it from Case).

 

select account.Name, (select Opportunity.Name from Account.Opportunities),  (Select CaseNumber from Cases), (select Task.Subject from Tasks)  from Account

 Any advice would be appreciated.

 

Thanks,

ThomasTTThomasTT

Since CaseId is stored in whatId in Task, can't be from Task, so why don't you start from Case?

 

 

SELECT
    Account.Name, Opportunity.Name, Name,
    (SELECT Subject from Tasks)
FROM
    Case
WHERE
    AccountId in :accList

 With ignoring cases which doesn't have any Task, you just loop Cases and it's Tasks and create a set of "Case.Account.Name, Case.Opportunity.Name, Name, Tasks[i].Subject".

 

ThomasTT

 
sforce2009sforce2009

Just in case, You might want ot check this blog on Joins, Groupby etc

http://blog.jeffdouglas.com/2010/02/22/soql-how-i-query-with-thee-let-me-count-the-ways/

 

Thanks
steven75steven75

Thanks ThomasTT,

 

I tried to plug the query you provided in salesforce.scheme in Eclipse, but it didn't work.  Unforturnately, I am very new to SalesForce programing, can you please explain little more how can I accomplish this?

 

Thanks,

Steven