Marketing Cloud SQL Query 1 to many only get result if only 1 record is present in that Data Extension The Next CEO of Stack Overflow2019 Community Moderator ElectionMismatch on Marketing Cloud query results vs Salesforce reportWriting a query to only match the first five digits of zip codesSQL Count Query Query Without Data Extension?Send an email to one contact with multiple ordersMarketing Cloud SQL QueryMarketing Cloud SQL to update Data ExtensionUsing query to populate target data extension for resending automated mailMarketing Cloud Mobile Connect Contacts have ID only - no other dataMarketing Cloud SQL statementGeneric System Error when I use a Left Join
The exact meaning of 'Mom made me a sandwich'
What did we know about the Kessel run before the prequels?
Dominated convergence theorem - what sequence?
Rotate a column
Where do students learn to solve polynomial equations these days?
Make solar eclipses exceedingly rare, but still have new moons
Is it convenient to ask the journal's editor for two additional days to complete a review?
Calculator final project in Python
Recycling old answers
Can you be charged for obstruction for refusing to answer questions?
Is a distribution that is normal, but highly skewed considered Gaussian?
Does Germany produce more waste than the US?
What steps are necessary to read a Modern SSD in Medieval Europe?
Is the D&D universe the same as the Forgotten Realms universe?
Domestic-to-international connection at Orlando (MCO)
How to count occurrences of text in a file?
Easy to read palindrome checker
Are police here, aren't itthey?
Why don't programming languages automatically manage the synchronous/asynchronous problem?
How I can get glyphs from a fraktur font and use them as identifiers?
Which one is the true statement?
Proper way to express "He disappeared them"
Why does standard notation not preserve intervals (visually)
How to delete every two lines after 3rd lines in a file contains very large number of lines?
Marketing Cloud SQL Query 1 to many only get result if only 1 record is present in that Data Extension
The Next CEO of Stack Overflow2019 Community Moderator ElectionMismatch on Marketing Cloud query results vs Salesforce reportWriting a query to only match the first five digits of zip codesSQL Count Query Query Without Data Extension?Send an email to one contact with multiple ordersMarketing Cloud SQL QueryMarketing Cloud SQL to update Data ExtensionUsing query to populate target data extension for resending automated mailMarketing Cloud Mobile Connect Contacts have ID only - no other dataMarketing Cloud SQL statementGeneric System Error when I use a Left Join
I know a thing or two about SQL Queries, but in this case i can't figure out what to do. So in this case we have a Contact_Salesforce DE where all contacts are in. We have another DE named Placement__c_Salesforce. The relationship between the two is one to many. The Placement DE is where the orders of the contacts are in. I want to make a SQL query which only adds a contact to a new DE when this contact did his first order and NOT get contacts in who already did a few orders.
This is the code as far as i have it:
select contact.id as Id
, contact.email as Email
, contact.firstname as FirstName
from contact_salesforce contact
left join placement__c_salesforce placement
on placement.candidate__c = contact.id
where ?????
Hope someone can help me.
Cheers
marketing-cloud sql
asked Mar 19 at 10:28
wfrankhuizenwfrankhuizen
808
add a comment |
I know a thing or two about SQL Queries, but in this case i can't figure out what to do. So in this case we have a Contact_Salesforce DE where all contacts are in. We have another DE named Placement__c_Salesforce. The relationship between the two is one to many. The Placement DE is where the orders of the contacts are in. I want to make a SQL query which only adds a contact to a new DE when this contact did his first order and NOT get contacts in who already did a few orders.
This is the code as far as i have it:
select contact.id as Id
, contact.email as Email
, contact.firstname as FirstName
from contact_salesforce contact
left join placement__c_salesforce placement
on placement.candidate__c = contact.id
where ?????
Hope someone can help me.
Cheers
marketing-cloud sql
asked Mar 19 at 10:28
wfrankhuizenwfrankhuizen
808
You can also create a new field in Contact object of type "rollup summary" where you can COUNT the number of orders. Then use this field in your SQL query.
– zeljazouli
Mar 19 at 11:45
I know but adding fields to an object is not needed if it's solvable via a SQL query.
– wfrankhuizen
Mar 19 at 11:52
add a comment |
I know a thing or two about SQL Queries, but in this case i can't figure out what to do. So in this case we have a Contact_Salesforce DE where all contacts are in. We have another DE named Placement__c_Salesforce. The relationship between the two is one to many. The Placement DE is where the orders of the contacts are in. I want to make a SQL query which only adds a contact to a new DE when this contact did his first order and NOT get contacts in who already did a few orders.
This is the code as far as i have it:
select contact.id as Id
, contact.email as Email
, contact.firstname as FirstName
from contact_salesforce contact
left join placement__c_salesforce placement
on placement.candidate__c = contact.id
where ?????
Hope someone can help me.
Cheers
marketing-cloud sql
asked Mar 19 at 10:28
wfrankhuizenwfrankhuizen
808
I know a thing or two about SQL Queries, but in this case i can't figure out what to do. So in this case we have a Contact_Salesforce DE where all contacts are in. We have another DE named Placement__c_Salesforce. The relationship between the two is one to many. The Placement DE is where the orders of the contacts are in. I want to make a SQL query which only adds a contact to a new DE when this contact did his first order and NOT get contacts in who already did a few orders.
This is the code as far as i have it:
select contact.id as Id
, contact.email as Email
, contact.firstname as FirstName
from contact_salesforce contact
left join placement__c_salesforce placement
on placement.candidate__c = contact.id
where ?????
Hope someone can help me.
Cheers
marketing-cloud sql
marketing-cloud sql
asked Mar 19 at 10:28
wfrankhuizenwfrankhuizen
808
asked Mar 19 at 10:28
wfrankhuizenwfrankhuizen
808
asked Mar 19 at 10:28
wfrankhuizenwfrankhuizen
808
asked Mar 19 at 10:28
wfrankhuizenwfrankhuizen
808
asked Mar 19 at 10:28
wfrankhuizenwfrankhuizen
808
808
You can also create a new field in Contact object of type "rollup summary" where you can COUNT the number of orders. Then use this field in your SQL query.
– zeljazouli
Mar 19 at 11:45
I know but adding fields to an object is not needed if it's solvable via a SQL query.
– wfrankhuizen
Mar 19 at 11:52
add a comment |
You can also create a new field in Contact object of type "rollup summary" where you can COUNT the number of orders. Then use this field in your SQL query.
– zeljazouli
Mar 19 at 11:45
I know but adding fields to an object is not needed if it's solvable via a SQL query.
– wfrankhuizen
Mar 19 at 11:52
You can also create a new field in Contact object of type "rollup summary" where you can COUNT the number of orders. Then use this field in your SQL query.
– zeljazouli
Mar 19 at 11:45
You can also create a new field in Contact object of type "rollup summary" where you can COUNT the number of orders. Then use this field in your SQL query.
– zeljazouli
Mar 19 at 11:45
I know but adding fields to an object is not needed if it's solvable via a SQL query.
– wfrankhuizen
Mar 19 at 11:52
I know but adding fields to an object is not needed if it's solvable via a SQL query.
– wfrankhuizen
Mar 19 at 11:52
add a comment |
1 Answer
1
active
oldest
votes
You could try using the SQL HAVING clause.
Assuming I've understood your objective; this should work as your WHERE clause:
WHERE contact.id in (
select candidate__c
from [placement__c_salesforce]
group by candidate__c
having count(1)=1
)
In short, this will only return records where contact.id is only found ONCE in [placement__c_salesforce]
edited Mar 19 at 11:31
Adam Spriggs
17.7k42147
answered Mar 19 at 10:49
Cameron RobertCameron Robert
929312
Yes that is exactly what i wanted. Your solution did the job. Thanks :)
– wfrankhuizen
Mar 19 at 11:38
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
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f254411%2fmarketing-cloud-sql-query-1-to-many-only-get-result-if-only-1-record-is-present%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could try using the SQL HAVING clause.
Assuming I've understood your objective; this should work as your WHERE clause:
WHERE contact.id in (
select candidate__c
from [placement__c_salesforce]
group by candidate__c
having count(1)=1
)
In short, this will only return records where contact.id is only found ONCE in [placement__c_salesforce]
edited Mar 19 at 11:31
Adam Spriggs
17.7k42147
answered Mar 19 at 10:49
Cameron RobertCameron Robert
929312
Yes that is exactly what i wanted. Your solution did the job. Thanks :)
– wfrankhuizen
Mar 19 at 11:38
add a comment |
You could try using the SQL HAVING clause.
Assuming I've understood your objective; this should work as your WHERE clause:
WHERE contact.id in (
select candidate__c
from [placement__c_salesforce]
group by candidate__c
having count(1)=1
)
In short, this will only return records where contact.id is only found ONCE in [placement__c_salesforce]
edited Mar 19 at 11:31
Adam Spriggs
17.7k42147
answered Mar 19 at 10:49
Cameron RobertCameron Robert
929312
Yes that is exactly what i wanted. Your solution did the job. Thanks :)
– wfrankhuizen
Mar 19 at 11:38
add a comment |
You could try using the SQL HAVING clause.
Assuming I've understood your objective; this should work as your WHERE clause:
WHERE contact.id in (
select candidate__c
from [placement__c_salesforce]
group by candidate__c
having count(1)=1
)
In short, this will only return records where contact.id is only found ONCE in [placement__c_salesforce]
edited Mar 19 at 11:31
Adam Spriggs
17.7k42147
answered Mar 19 at 10:49
Cameron RobertCameron Robert
929312
You could try using the SQL HAVING clause.
Assuming I've understood your objective; this should work as your WHERE clause:
WHERE contact.id in (
select candidate__c
from [placement__c_salesforce]
group by candidate__c
having count(1)=1
)
In short, this will only return records where contact.id is only found ONCE in [placement__c_salesforce]
edited Mar 19 at 11:31
Adam Spriggs
17.7k42147
answered Mar 19 at 10:49
Cameron RobertCameron Robert
929312
edited Mar 19 at 11:31
Adam Spriggs
17.7k42147
edited Mar 19 at 11:31
Adam Spriggs
17.7k42147
edited Mar 19 at 11:31
Adam Spriggs
17.7k42147
17.7k42147
answered Mar 19 at 10:49
Cameron RobertCameron Robert
929312
answered Mar 19 at 10:49
Cameron RobertCameron Robert
929312
answered Mar 19 at 10:49
Cameron RobertCameron Robert
929312
929312
Yes that is exactly what i wanted. Your solution did the job. Thanks :)
– wfrankhuizen
Mar 19 at 11:38
add a comment |
Yes that is exactly what i wanted. Your solution did the job. Thanks :)
– wfrankhuizen
Mar 19 at 11:38
Yes that is exactly what i wanted. Your solution did the job. Thanks :)
– wfrankhuizen
Mar 19 at 11:38
Yes that is exactly what i wanted. Your solution did the job. Thanks :)
– wfrankhuizen
Mar 19 at 11:38
add a comment |
Thanks for contributing an answer to Salesforce Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f254411%2fmarketing-cloud-sql-query-1-to-many-only-get-result-if-only-1-record-is-present%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You can also create a new field in Contact object of type "rollup summary" where you can COUNT the number of orders. Then use this field in your SQL query.
– zeljazouli
Mar 19 at 11:45
I know but adding fields to an object is not needed if it's solvable via a SQL query.
– wfrankhuizen
Mar 19 at 11:52