function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Kamran-RaoKamran-Rao 

Transle/Convert Query With Inner Join in SOQL

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

Message Edited by Kamran-Rao on 07-20-2009 04:40 AM
Message Edited by Kamran-Rao on 07-20-2009 05:12 AM
Best Answer chosen by Admin (Salesforce Developers) 
Kamran-RaoKamran-Rao

Simon, Thanks a lot for you feed back. You are correct in current scenario, that I just wrote pseudo to give the idea what I want to do. Can you please elaborate how many levels nested queries are supported in SOQL?

 

Thanks again. 

 

Regards,

Rao

All Answers

SuperfellSuperfell

as you're only selecting id from opportunity, you don't need the extra nesting, you should be able to do something like this.

 

 

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

and OPPORTUNITY__C in (select Opportunity_Id__c from Contract)  

Kamran-RaoKamran-Rao

Simon, Thanks a lot for you feed back. You are correct in current scenario, that I just wrote pseudo to give the idea what I want to do. Can you please elaborate how many levels nested queries are supported in SOQL?

 

Thanks again. 

 

Regards,

Rao

This was selected as the best answer
SuperfellSuperfell
It should be in the SOQL docs, i don't think you can nest semi-joins.
Kamran-RaoKamran-Rao

Simon,

 

I need a favor from you. I've been trapped in a similar query that I have posted on the following link:
http://community.salesforce.com/sforce/board/message?board.id=general_development&message.id=32464&jump=true

 

Would you please have a look at the 1st post in above thread to get to the problem and give your expert openion.

 

Thanks,
Rao