Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the following object structure

Store (parent) : Investment (child)
Site : Impact (junction object) : Store

I'm trying to construct a query to display the results in a pageblocktable. The results need to be filtered to only show records where there is an impact and to show only the last investment for that store.

Ive currently come up with the following query:

[Select s.Name, 
    (Select Name, ActualImpactCash__c, Actual_Impact__c, ForecastImpact__c, Forecast_Impact__c, Site__c 
    From Pipeline_Impacts1__r), 
    (Select InvestmentType__c, Investment_Date__c, actualReLaunchDate__c, Investment_Type__c, Launch_Date__c 
    From Investment_Programs__r order by Investment_date__c desc limit 1) 
From Store__c s]

The problem i'm having is excluding those stores that dont have an impact associated. Any ideas?

Cheers Paul

share|improve this question
up vote 3 down vote accepted

Probably all you need is to add

WHERE Id IN (SELECT Store__c FROM Pipeline_Impact__c)

at the end of your query. At least if I use this translation table of your situation similar query works for me:

Store = Contact, Investment = Case (Contact can have multiple Cases associated)
Site = Opportunity, junction = OpportunityContactRole

    (SELECT Subject FROM Cases),
    (SELECT Id FROM OpportunityContactRoles LIMIT 1)
FROM Contact

This returns only such Contacts that have Cases, blah blah blah ;)

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.