Hi,

Can somebody help me in tranlating an SQL query in SOQL. Here is the query:

Select Opportunity.Id
FROM Opportunity
inner join Contract
on Opportunity.Id = Contract.Opportunity_Id__c

I have modified this query and it works fine, following is the modified query:

Select Id FROM Opportunity
Where Id in (select Opportunity_Id__c from Contract).

Now I need to put this query in another sub query on the same pattern and it gives me an error:

select Id FROM Media_Campaign__c
where Id in (Select Opportunity_Media_Campaign__c FROM Contract_Campaign__c )

and OPPORTUNITY__C in (Select Id FROM Opportunity

                                        Where Id in (select Opportunity_Id__c from Contract)) 

Error: Nesting of semi Joins subselects is not supported:

Here is the respective sql pseudo translation of above query that is givnig me the error.

select mc.mediaCapaignID, cc.contractCampaignID
from mediaCamp__c mc
left outer join contractCampaign__c cc
on mc.mediaCapaignID == cc.contractCampaignID
where mc.opportunityID in
 (select opp.opportunityID
 from opportunity opp
 inner join contract con
 on opp.opportunityID = con.opportunityID
 where con.RegionID = 'Fr'

Thanking you in anticipatioin. 

Regards,

Rao