Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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
add comment

1 Answer

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 Id, 
    (SELECT Subject FROM Cases),
    (SELECT Id FROM OpportunityContactRoles LIMIT 1)
FROM Contact
WHERE Id IN (SELECT ContactId FROM Case)

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

share|improve this answer
add comment

Your Answer

 
discard

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.