Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MarkL.ax269MarkL.ax269 

semi-join question

I've been very interested in the new semi-join feature because I was sure it would solve a problem I've been having.  But now I'm not so sure.  From the documentation, here's a simple semi-join:

select Id, Name from Account where Id in (select AccountId from Opportunity where StageName = 'Closed Lost')

How is that any more helpful than this query?  Both return exactly the same thing:

select Account.Id, Account.Name from Opportunity where StageName = 'Closed Lost'

Here's an example of what I really need to accomplish, and so far I'm not able to get this query to work.  This would pull a list of all Contacts on Accounts with a lost Opportunity.  It fails telling me "Cannot use a foreign key for outer field for semi join"

select Id from Contact where AccountId in (select AccountId from Opportunity where StageName = 'Closed Lost')

Is this kind of query possible?  Or is there another way to get it done?

Mark
MarkL.ax269MarkL.ax269
Seems it takes actually posting a question to figure out the answer.  I turned it inside-out and got it working right after posting.  :)

select Id, (select Id from Contacts) from Account where Id in (select AccountId from Opportunity where StageName = 'Closed Lost')

Mark
SuperfellSuperfell
Your first 2 queries are not the same, the 1st query will return a distinct set of accounts, while the 2nd will not (have 5 oppties that are closed lost for the same account to see the difference).

For your last question, remember that you can still use SOQL-R, so you can do
select Id, (select id, name from contacts) from Account where Id in (select AccountId from Opportunity where StageName = 'Closed Lost')
MarkL.ax269MarkL.ax269
True, but it's relatively little overhead to get the distinct accounts from the opportunity query as long as the result set isn't too large.  Meanwhile the new capability has solved a big issue for me, which is great!  Thanks Simon.

Mark