SOQL - Select only contacts that have related Opportunities

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

I have the following SOQL:

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) FROM Contact WHERE Email = 'test@test.com'

I want to restrict this to return only those contacts for which the related Opportunities list is not empty. How can I do this?

Thanks.

edited Sep 5 at 13:16

David Reed

20.5k31640

asked Sep 5 at 13:15

Cosmin C.

add a commentÂ |Â

up vote
1
down vote

favorite

I have the following SOQL:

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) FROM Contact WHERE Email = 'test@test.com'

I want to restrict this to return only those contacts for which the related Opportunities list is not empty. How can I do this?

Thanks.

edited Sep 5 at 13:16

David Reed

20.5k31640

asked Sep 5 at 13:15

Cosmin C.

add a commentÂ |Â

up vote
1
down vote

favorite

I have the following SOQL:

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) FROM Contact WHERE Email = 'test@test.com'

I want to restrict this to return only those contacts for which the related Opportunities list is not empty. How can I do this?

Thanks.

edited Sep 5 at 13:16

David Reed

20.5k31640

asked Sep 5 at 13:15

Cosmin C.

I have the following SOQL:

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) FROM Contact WHERE Email = 'test@test.com'

I want to restrict this to return only those contacts for which the related Opportunities list is not empty. How can I do this?

Thanks.

soql opportunity contact

edited Sep 5 at 13:16

David Reed

20.5k31640

asked Sep 5 at 13:15

Cosmin C.

edited Sep 5 at 13:16

David Reed

20.5k31640

asked Sep 5 at 13:15

Cosmin C.

edited Sep 5 at 13:16

David Reed

20.5k31640

edited Sep 5 at 13:16

David Reed

20.5k31640

edited Sep 5 at 13:16

David Reed

20.5k31640

asked Sep 5 at 13:15

Cosmin C.

asked Sep 5 at 13:15

Cosmin C.

asked Sep 5 at 13:15

Cosmin C.

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
5
down vote

accepted

This is a good place to apply a semi-join pattern, which is the most common query tool used to limit a query by the presence or absence of related objects. It is made only a little bit more complex here by the fact that Opportunity and Contact are related by a sort of junction object, OpportunityContactRole:

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) 
FROM Contact 
WHERE Email = 'test@test.com'
 AND Id IN (SELECT ContactId
 FROM OpportunityContactRole)

answered Sep 5 at 13:20

David Reed

20.5k31640

Thanks, exactly what I needed
â€“Â Cosmin C.
Sep 6 at 7:55

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "459"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f231316%2fsoql-select-only-contacts-that-have-related-opportunities%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
5
down vote

accepted

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) 
FROM Contact 
WHERE Email = 'test@test.com'
 AND Id IN (SELECT ContactId
 FROM OpportunityContactRole)

answered Sep 5 at 13:20

David Reed

20.5k31640

Thanks, exactly what I needed
â€“Â Cosmin C.
Sep 6 at 7:55

add a commentÂ |Â

up vote
5
down vote

accepted

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) 
FROM Contact 
WHERE Email = 'test@test.com'
 AND Id IN (SELECT ContactId
 FROM OpportunityContactRole)

answered Sep 5 at 13:20

David Reed

20.5k31640

Thanks, exactly what I needed
â€“Â Cosmin C.
Sep 6 at 7:55

add a commentÂ |Â

up vote
5
down vote

accepted

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) 
FROM Contact 
WHERE Email = 'test@test.com'
 AND Id IN (SELECT ContactId
 FROM OpportunityContactRole)

answered Sep 5 at 13:20

David Reed

20.5k31640

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) 
FROM Contact 
WHERE Email = 'test@test.com'
 AND Id IN (SELECT ContactId
 FROM OpportunityContactRole)

answered Sep 5 at 13:20

David Reed

20.5k31640

answered Sep 5 at 13:20

David Reed

20.5k31640

answered Sep 5 at 13:20

David Reed

20.5k31640

answered Sep 5 at 13:20

David Reed

20.5k31640

Thanks, exactly what I needed
â€“Â Cosmin C.
Sep 6 at 7:55

add a commentÂ |Â

Thanks, exactly what I needed
â€“Â Cosmin C.
Sep 6 at 7:55

Thanks, exactly what I needed
â€“Â Cosmin C.
Sep 6 at 7:55

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

搜尋此網誌

mjhjmtu