Etydhv

Why is B♯ higher than C♭ in 31-ET?

If prion is a protein. Why is it not disassembled by the digestive system?

Is it cheaper to drop cargo than to land it?

If 1. e4 c6 is considered as a sound defense for black, why is 1. c3 so rare?

What is a "listed natural gas appliance"?

Why do money exchangers give different rates to different bills?

Casual versus formal jacket

Theorem won't go to multiple lines and is causing text to run off the page

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

What happens to matryoshka Mordenkainen's Magnificent Mansions?

I caught several of my students plagiarizing. Could it be my fault as a teacher?

Sub query result is 0

Where can I go to avoid planes overhead?

Upside-Down Pyramid Addition...REVERSED!

Why wasn't the Night King naked in S08E03?

How could a planet have most of its water in the atmosphere?

Is one octave above tonic also considered as tonic?

In Endgame, why were these characters still around?

Are we obligated to aspire to be Talmidei Chachamim?

Quoting Yourself

What is the unit of the area when geometry attributes are calculated in QGIS?

Identifying my late father's D&D stuff found in the attic

Can fracking help reduce CO2?

Why is C# in the D Major Scale?

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 2 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 2 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 2 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 2 hours ago

Dan WoodingDan Wooding

1,9911539

share|improve this question

asked 2 hours ago

Dan WoodingDan Wooding

1,9911539

asked 2 hours ago

Dan WoodingDan Wooding

1,9911539

asked 2 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 2 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 2 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 2 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 2 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 2 hours ago

David Reed♦David Reed

40.8k82362

answered 2 hours ago

David Reed♦David Reed

40.8k82362

answered 2 hours ago

David Reed♦David Reed

40.8k82362