SQL Server, excluding many rows with NOT IN aside from a few rows?
Is there a clean way to add to this subquery to not remove seven specific rows
AND sa.AccountID NOT IN (
SELECT AccountID
FROM
SubAccountData
WHERE AccountDataTypeID = 10315
AND
Data IN (
SELECT KeyData
FROM AccountDataValues
WHERE AccountDataTypeID = 10315
AND
ValueData LIKE 'FS001%MPLS'
)
)
This pulls roughly 1000 rows and there are 7 I do not want to pull. I have the sa.AccountID.
sql sql-server relational-database
add a comment |
Is there a clean way to add to this subquery to not remove seven specific rows
AND sa.AccountID NOT IN (
SELECT AccountID
FROM
SubAccountData
WHERE AccountDataTypeID = 10315
AND
Data IN (
SELECT KeyData
FROM AccountDataValues
WHERE AccountDataTypeID = 10315
AND
ValueData LIKE 'FS001%MPLS'
)
)
This pulls roughly 1000 rows and there are 7 I do not want to pull. I have the sa.AccountID.
sql sql-server relational-database
add a comment |
Is there a clean way to add to this subquery to not remove seven specific rows
AND sa.AccountID NOT IN (
SELECT AccountID
FROM
SubAccountData
WHERE AccountDataTypeID = 10315
AND
Data IN (
SELECT KeyData
FROM AccountDataValues
WHERE AccountDataTypeID = 10315
AND
ValueData LIKE 'FS001%MPLS'
)
)
This pulls roughly 1000 rows and there are 7 I do not want to pull. I have the sa.AccountID.
sql sql-server relational-database
Is there a clean way to add to this subquery to not remove seven specific rows
AND sa.AccountID NOT IN (
SELECT AccountID
FROM
SubAccountData
WHERE AccountDataTypeID = 10315
AND
Data IN (
SELECT KeyData
FROM AccountDataValues
WHERE AccountDataTypeID = 10315
AND
ValueData LIKE 'FS001%MPLS'
)
)
This pulls roughly 1000 rows and there are 7 I do not want to pull. I have the sa.AccountID.
sql sql-server relational-database
sql sql-server relational-database
edited Nov 15 '18 at 18:50
Sami
9,04831243
9,04831243
asked Nov 15 '18 at 18:39
Matt LarsonMatt Larson
353213
353213
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Since it's a NOT IN
you just need to exclude the rows from being returned from the subquery:
... NOT IN (SELECT AccountID
FROM SubAccountData
WHERE AccountID NOT IN (the 7 IDs)
AND AccountDateTypeID = 10315
AND Data IN (...)
)
Yep, that does the trick. Thanks so much!
– Matt Larson
Nov 15 '18 at 18:57
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53325959%2fsql-server-excluding-many-rows-with-not-in-aside-from-a-few-rows%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
Since it's a NOT IN
you just need to exclude the rows from being returned from the subquery:
... NOT IN (SELECT AccountID
FROM SubAccountData
WHERE AccountID NOT IN (the 7 IDs)
AND AccountDateTypeID = 10315
AND Data IN (...)
)
Yep, that does the trick. Thanks so much!
– Matt Larson
Nov 15 '18 at 18:57
add a comment |
Since it's a NOT IN
you just need to exclude the rows from being returned from the subquery:
... NOT IN (SELECT AccountID
FROM SubAccountData
WHERE AccountID NOT IN (the 7 IDs)
AND AccountDateTypeID = 10315
AND Data IN (...)
)
Yep, that does the trick. Thanks so much!
– Matt Larson
Nov 15 '18 at 18:57
add a comment |
Since it's a NOT IN
you just need to exclude the rows from being returned from the subquery:
... NOT IN (SELECT AccountID
FROM SubAccountData
WHERE AccountID NOT IN (the 7 IDs)
AND AccountDateTypeID = 10315
AND Data IN (...)
)
Since it's a NOT IN
you just need to exclude the rows from being returned from the subquery:
... NOT IN (SELECT AccountID
FROM SubAccountData
WHERE AccountID NOT IN (the 7 IDs)
AND AccountDateTypeID = 10315
AND Data IN (...)
)
answered Nov 15 '18 at 18:52
Aaron DietzAaron Dietz
8,5471924
8,5471924
Yep, that does the trick. Thanks so much!
– Matt Larson
Nov 15 '18 at 18:57
add a comment |
Yep, that does the trick. Thanks so much!
– Matt Larson
Nov 15 '18 at 18:57
Yep, that does the trick. Thanks so much!
– Matt Larson
Nov 15 '18 at 18:57
Yep, that does the trick. Thanks so much!
– Matt Larson
Nov 15 '18 at 18:57
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53325959%2fsql-server-excluding-many-rows-with-not-in-aside-from-a-few-rows%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