Report based on 4 Objects inc junction object

Question

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

Answer 1

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 ;)