Mdthbs

For a benzene shown in a skeletal structure, what does a substituent to the center of the ring mean?

Type-check an expression

How to give very negative feedback gracefully?

What happens if I start too many background jobs?

How can I get a job without pushing my family's income into a higher tax bracket?

If Earth is tilted, why is Polaris always above the same spot?

Identifying a transmission to myself

How to reply this mail from potential PhD professor?

Pressure inside an infinite ocean?

Can I get a paladin's steed by True Polymorphing into a monster that can cast Find Steed?

Did we get closer to another plane than we were supposed to, or was the pilot just protecting our delicate sensibilities?

Is induction neccessary for proving that every injective mapping of a finite set into itself is a mapping onto itself?

Does this article imply that Turing-Computability is not the same as "effectively computable"?

Should I replace my bicycle tires if they have not been inflated in multiple years

Returning the outputs of a nested structure

Pawn Promotion Double Checks

Should one double the thirds or the fifth in chords?

Why do money exchangers give different rates to different bills?

Sub query result is 0

Do I have to make someone coauthor if he/she solves a problem in StackExchange, asked by myself, which is later used in my paper?

My ID is expired, can I fly to the Bahamas with my passport?

To customize a predefined symbol with different colors

What are the spoon bit of a spoon and fork bit of a fork called?

What is a "listed natural gas appliance"?

Sub query result is 0

With junction object - how to merge these 2 queries?Filter and Query in Apex code?Add Products to a New OpportunityHow do I link AccountTeamMembers to Contact objects?SOQL Results differ from Salesforce ReportQuery Account team and concatenate resultsCombining Multiple Rows From SOQL Query In DataLoaderCloning Opportunities Not Calculating Expected RevenueHow to query Many.Master.Lookup relationship?Conga Composer: Query Salesforce Files from Products to Opportunity

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

1

I want to get the Opportunity Ids of the Opportunity records that have associated OpportunityLineItems but no Revenue_Pipeline__c custom object records for a batch class.

I have my sub-query which gives me the results if the records are there, but I can't seem to figure out to filter if there are no results?

SELECT Id, Name,

(SELECT Id, Name FROM Revenue_Pipelines__r),

(SELECT Id, Product2Id FROM OpportunityLineItems)

FROM Opportunity 

WHERE NPD_Forecast_Category__c = 'Renew'

Is there a way to filter in the query to get the Opps that do not have a Revenue Pipeline but do have an Opportunityline. Or do I need to loop through the results and check each one then do the logic after that?

apex query subquery

share|improve this question

asked 4 hours ago

Dan WoodingDan Wooding

1,9911539

add a comment | 

1

I want to get the Opportunity Ids of the Opportunity records that have associated OpportunityLineItems but no Revenue_Pipeline__c custom object records for a batch class.

I have my sub-query which gives me the results if the records are there, but I can't seem to figure out to filter if there are no results?

SELECT Id, Name,

(SELECT Id, Name FROM Revenue_Pipelines__r),

(SELECT Id, Product2Id FROM OpportunityLineItems)

FROM Opportunity 

WHERE NPD_Forecast_Category__c = 'Renew'

Is there a way to filter in the query to get the Opps that do not have a Revenue Pipeline but do have an Opportunityline. Or do I need to loop through the results and check each one then do the logic after that?

apex query subquery

share|improve this question

asked 4 hours ago

Dan WoodingDan Wooding

1,9911539

add a comment | 

I want to get the Opportunity Ids of the Opportunity records that have associated OpportunityLineItems but no Revenue_Pipeline__c custom object records for a batch class.

I have my sub-query which gives me the results if the records are there, but I can't seem to figure out to filter if there are no results?

SELECT Id, Name,

(SELECT Id, Name FROM Revenue_Pipelines__r),

(SELECT Id, Product2Id FROM OpportunityLineItems)

FROM Opportunity 

WHERE NPD_Forecast_Category__c = 'Renew'

Is there a way to filter in the query to get the Opps that do not have a Revenue Pipeline but do have an Opportunityline. Or do I need to loop through the results and check each one then do the logic after that?

apex query subquery

share|improve this question

asked 4 hours ago

Dan WoodingDan Wooding

1,9911539

SELECT Id, Name,

(SELECT Id, Name FROM Revenue_Pipelines__r),

(SELECT Id, Product2Id FROM OpportunityLineItems)

FROM Opportunity 

WHERE NPD_Forecast_Category__c = 'Renew'

share|improve this question

asked 4 hours ago

Dan WoodingDan Wooding

1,9911539

share|improve this question

asked 4 hours ago

Dan WoodingDan Wooding

1,9911539

asked 4 hours ago

Dan WoodingDan Wooding

1,9911539

asked 4 hours ago

Dan WoodingDan Wooding

1,9911539

1 Answer
1

active

oldest

votes

3

The usual pattern is to use a semi-join:

SELECT Id, Name,

(SELECT Id, Product2Id FROM OpportunityLineItems)

FROM Opportunity 

WHERE NPD_Forecast_Category__c = 'Renew'

      AND Id NOT IN (SELECT Opportunity_Id__c FROM Revenue_Pipeline__c)

This pattern lets the database engine do some of the work for you, so there's no need to loop in Apex and you can return significantly less data.

See the SOQL documentation for more details - there are some implementation restrictions around using semi- and anti-joins.

share|improve this answer

answered 4 hours ago

David Reed♦David Reed

40.8k82362

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',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f260640%2fsub-query-result-is-0%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

3

The usual pattern is to use a semi-join:

SELECT Id, Name,

(SELECT Id, Product2Id FROM OpportunityLineItems)

FROM Opportunity 

WHERE NPD_Forecast_Category__c = 'Renew'

      AND Id NOT IN (SELECT Opportunity_Id__c FROM Revenue_Pipeline__c)

This pattern lets the database engine do some of the work for you, so there's no need to loop in Apex and you can return significantly less data.

See the SOQL documentation for more details - there are some implementation restrictions around using semi- and anti-joins.

share|improve this answer

answered 4 hours ago

David Reed♦David Reed

40.8k82362

add a comment | 

3

The usual pattern is to use a semi-join:

SELECT Id, Name,

(SELECT Id, Product2Id FROM OpportunityLineItems)

FROM Opportunity 

WHERE NPD_Forecast_Category__c = 'Renew'

      AND Id NOT IN (SELECT Opportunity_Id__c FROM Revenue_Pipeline__c)

This pattern lets the database engine do some of the work for you, so there's no need to loop in Apex and you can return significantly less data.

See the SOQL documentation for more details - there are some implementation restrictions around using semi- and anti-joins.

share|improve this answer

answered 4 hours ago

David Reed♦David Reed

40.8k82362

add a comment | 

The usual pattern is to use a semi-join:

SELECT Id, Name,

(SELECT Id, Product2Id FROM OpportunityLineItems)

FROM Opportunity 

WHERE NPD_Forecast_Category__c = 'Renew'

      AND Id NOT IN (SELECT Opportunity_Id__c FROM Revenue_Pipeline__c)

This pattern lets the database engine do some of the work for you, so there's no need to loop in Apex and you can return significantly less data.

See the SOQL documentation for more details - there are some implementation restrictions around using semi- and anti-joins.

share|improve this answer

answered 4 hours ago

David Reed♦David Reed

40.8k82362

SELECT Id, Name,

(SELECT Id, Product2Id FROM OpportunityLineItems)

FROM Opportunity 

WHERE NPD_Forecast_Category__c = 'Renew'

      AND Id NOT IN (SELECT Opportunity_Id__c FROM Revenue_Pipeline__c)

share|improve this answer

answered 4 hours ago

David Reed♦David Reed

40.8k82362

answered 4 hours ago

David Reed♦David Reed

40.8k82362

answered 4 hours ago

David Reed♦David Reed

40.8k82362