how to query for unpaired values of a property
I have a table with TransactionIDs and TransactionTypes. I have 2 types of transactions (deposits and cash outs) and each transaction has a transaction ID. I need to retrieve the transaction IDs that are attributed only to deposits and not to cash outs.
This is the Transactions table:
TransactionType TransactionID
--------------------------------
Deposit 1234
Cashout 1234
Cashout 12345
Cashout 123456
Deposit 1256
Deposit 12345
Deposit 123456
Deposit 777
This is the result I need:
TransactionType TransactionID
--------------------------------
Deposit 1256
Deposit 777
Can anyone help me with a sql query that gives me the needed result?
Thanks in advance!
sql
asked Nov 16 '18 at 9:51
Lo RiLo Ri
52
add a comment |
I have a table with TransactionIDs and TransactionTypes. I have 2 types of transactions (deposits and cash outs) and each transaction has a transaction ID. I need to retrieve the transaction IDs that are attributed only to deposits and not to cash outs.
This is the Transactions table:
TransactionType TransactionID
--------------------------------
Deposit 1234
Cashout 1234
Cashout 12345
Cashout 123456
Deposit 1256
Deposit 12345
Deposit 123456
Deposit 777
This is the result I need:
TransactionType TransactionID
--------------------------------
Deposit 1256
Deposit 777
Can anyone help me with a sql query that gives me the needed result?
Thanks in advance!
sql
asked Nov 16 '18 at 9:51
Lo RiLo Ri
52
Please, provide sample table data and the expected result as formatted text, not images (or links to images.)
– jarlh
Nov 16 '18 at 9:52
add a comment |
I have a table with TransactionIDs and TransactionTypes. I have 2 types of transactions (deposits and cash outs) and each transaction has a transaction ID. I need to retrieve the transaction IDs that are attributed only to deposits and not to cash outs.
This is the Transactions table:
TransactionType TransactionID
--------------------------------
Deposit 1234
Cashout 1234
Cashout 12345
Cashout 123456
Deposit 1256
Deposit 12345
Deposit 123456
Deposit 777
This is the result I need:
TransactionType TransactionID
--------------------------------
Deposit 1256
Deposit 777
Can anyone help me with a sql query that gives me the needed result?
Thanks in advance!
sql
asked Nov 16 '18 at 9:51
Lo RiLo Ri
52
I have a table with TransactionIDs and TransactionTypes. I have 2 types of transactions (deposits and cash outs) and each transaction has a transaction ID. I need to retrieve the transaction IDs that are attributed only to deposits and not to cash outs.
This is the Transactions table:
TransactionType TransactionID
--------------------------------
Deposit 1234
Cashout 1234
Cashout 12345
Cashout 123456
Deposit 1256
Deposit 12345
Deposit 123456
Deposit 777
This is the result I need:
TransactionType TransactionID
--------------------------------
Deposit 1256
Deposit 777
Can anyone help me with a sql query that gives me the needed result?
Thanks in advance!
sql
sql
asked Nov 16 '18 at 9:51
Lo RiLo Ri
52
asked Nov 16 '18 at 9:51
Lo RiLo Ri
52
edited Nov 16 '18 at 11:51
Lo Ri
asked Nov 16 '18 at 9:51
Lo RiLo Ri
52
asked Nov 16 '18 at 9:51
Lo RiLo Ri
52
asked Nov 16 '18 at 9:51
Lo RiLo Ri
52
52
Please, provide sample table data and the expected result as formatted text, not images (or links to images.)
– jarlh
Nov 16 '18 at 9:52
add a comment |
Please, provide sample table data and the expected result as formatted text, not images (or links to images.)
– jarlh
Nov 16 '18 at 9:52
Please, provide sample table data and the expected result as formatted text, not images (or links to images.)
– jarlh
Nov 16 '18 at 9:52
Please, provide sample table data and the expected result as formatted text, not images (or links to images.)
– jarlh
Nov 16 '18 at 9:52
add a comment |
3 Answers
3
active
oldest
votes
Try with the help of subquery:
select transactionId from <table> where transactiontType = 'Deposit'
and transactionId not in
(select transactionId from <table> where transactiontType = 'Cashout');
In this way you can filter out transaction ids with cash out as transaction type
answered Nov 16 '18 at 9:58
codeLovercodeLover
2,2551620
Thank you, codeLover!
– Lo Ri
Nov 16 '18 at 12:20
add a comment |
I would use NOT EXISTS :
SELECT t.*
FROM table t
WHERE transactiontType = 'Deposit' AND
NOT EXISTS (SELECT 1 FROM table t1 WHERE t1.transactionid = t.transactionid AND t1.transactiontType <> t.transactiontType);
answered Nov 16 '18 at 10:06
Yogesh SharmaYogesh Sharma
34.3k51440
Thank you, Yogesh! :)
– Lo Ri
Nov 16 '18 at 12:19
add a comment |
Use EXCEPT to return the transactionId's with deposit, except those with Cashout:
select transactionId from tablename where transactiontType = 'Deposit'
except
select transactionId from tablename where transactiontType = 'Cashout'
answered Nov 16 '18 at 10:00
jarlhjarlh
29.8k52138
Thank you, jarlh! :)
– Lo Ri
Nov 16 '18 at 12:19
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%2f53335282%2fhow-to-query-for-unpaired-values-of-a-property%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try with the help of subquery:
select transactionId from <table> where transactiontType = 'Deposit'
and transactionId not in
(select transactionId from <table> where transactiontType = 'Cashout');
In this way you can filter out transaction ids with cash out as transaction type
answered Nov 16 '18 at 9:58
codeLovercodeLover
2,2551620
Thank you, codeLover!
– Lo Ri
Nov 16 '18 at 12:20
add a comment |
Try with the help of subquery:
select transactionId from <table> where transactiontType = 'Deposit'
and transactionId not in
(select transactionId from <table> where transactiontType = 'Cashout');
In this way you can filter out transaction ids with cash out as transaction type
answered Nov 16 '18 at 9:58
codeLovercodeLover
2,2551620
Thank you, codeLover!
– Lo Ri
Nov 16 '18 at 12:20
add a comment |
Try with the help of subquery:
select transactionId from <table> where transactiontType = 'Deposit'
and transactionId not in
(select transactionId from <table> where transactiontType = 'Cashout');
In this way you can filter out transaction ids with cash out as transaction type
answered Nov 16 '18 at 9:58
codeLovercodeLover
2,2551620
Try with the help of subquery:
select transactionId from <table> where transactiontType = 'Deposit'
and transactionId not in
(select transactionId from <table> where transactiontType = 'Cashout');
In this way you can filter out transaction ids with cash out as transaction type
answered Nov 16 '18 at 9:58
codeLovercodeLover
2,2551620
answered Nov 16 '18 at 9:58
codeLovercodeLover
2,2551620
answered Nov 16 '18 at 9:58
codeLovercodeLover
2,2551620
answered Nov 16 '18 at 9:58
codeLovercodeLover
2,2551620
2,2551620
Thank you, codeLover!
– Lo Ri
Nov 16 '18 at 12:20
add a comment |
Thank you, codeLover!
– Lo Ri
Nov 16 '18 at 12:20
Thank you, codeLover!
– Lo Ri
Nov 16 '18 at 12:20
Thank you, codeLover!
– Lo Ri
Nov 16 '18 at 12:20
add a comment |
I would use NOT EXISTS :
SELECT t.*
FROM table t
WHERE transactiontType = 'Deposit' AND
NOT EXISTS (SELECT 1 FROM table t1 WHERE t1.transactionid = t.transactionid AND t1.transactiontType <> t.transactiontType);
answered Nov 16 '18 at 10:06
Yogesh SharmaYogesh Sharma
34.3k51440
Thank you, Yogesh! :)
– Lo Ri
Nov 16 '18 at 12:19
add a comment |
I would use NOT EXISTS :
SELECT t.*
FROM table t
WHERE transactiontType = 'Deposit' AND
NOT EXISTS (SELECT 1 FROM table t1 WHERE t1.transactionid = t.transactionid AND t1.transactiontType <> t.transactiontType);
answered Nov 16 '18 at 10:06
Yogesh SharmaYogesh Sharma
34.3k51440
Thank you, Yogesh! :)
– Lo Ri
Nov 16 '18 at 12:19
add a comment |
I would use NOT EXISTS :
SELECT t.*
FROM table t
WHERE transactiontType = 'Deposit' AND
NOT EXISTS (SELECT 1 FROM table t1 WHERE t1.transactionid = t.transactionid AND t1.transactiontType <> t.transactiontType);
answered Nov 16 '18 at 10:06
Yogesh SharmaYogesh Sharma
34.3k51440
I would use NOT EXISTS :
SELECT t.*
FROM table t
WHERE transactiontType = 'Deposit' AND
NOT EXISTS (SELECT 1 FROM table t1 WHERE t1.transactionid = t.transactionid AND t1.transactiontType <> t.transactiontType);
answered Nov 16 '18 at 10:06
Yogesh SharmaYogesh Sharma
34.3k51440
answered Nov 16 '18 at 10:06
Yogesh SharmaYogesh Sharma
34.3k51440
answered Nov 16 '18 at 10:06
Yogesh SharmaYogesh Sharma
34.3k51440
answered Nov 16 '18 at 10:06
Yogesh SharmaYogesh Sharma
34.3k51440
34.3k51440
Thank you, Yogesh! :)
– Lo Ri
Nov 16 '18 at 12:19
add a comment |
Thank you, Yogesh! :)
– Lo Ri
Nov 16 '18 at 12:19
Thank you, Yogesh! :)
– Lo Ri
Nov 16 '18 at 12:19
Thank you, Yogesh! :)
– Lo Ri
Nov 16 '18 at 12:19
add a comment |
Use EXCEPT to return the transactionId's with deposit, except those with Cashout:
select transactionId from tablename where transactiontType = 'Deposit'
except
select transactionId from tablename where transactiontType = 'Cashout'
answered Nov 16 '18 at 10:00
jarlhjarlh
29.8k52138
Thank you, jarlh! :)
– Lo Ri
Nov 16 '18 at 12:19
add a comment |
Use EXCEPT to return the transactionId's with deposit, except those with Cashout:
select transactionId from tablename where transactiontType = 'Deposit'
except
select transactionId from tablename where transactiontType = 'Cashout'
answered Nov 16 '18 at 10:00
jarlhjarlh
29.8k52138
Thank you, jarlh! :)
– Lo Ri
Nov 16 '18 at 12:19
add a comment |
Use EXCEPT to return the transactionId's with deposit, except those with Cashout:
select transactionId from tablename where transactiontType = 'Deposit'
except
select transactionId from tablename where transactiontType = 'Cashout'
answered Nov 16 '18 at 10:00
jarlhjarlh
29.8k52138
Use EXCEPT to return the transactionId's with deposit, except those with Cashout:
select transactionId from tablename where transactiontType = 'Deposit'
except
select transactionId from tablename where transactiontType = 'Cashout'
answered Nov 16 '18 at 10:00
jarlhjarlh
29.8k52138
answered Nov 16 '18 at 10:00
jarlhjarlh
29.8k52138
answered Nov 16 '18 at 10:00
jarlhjarlh
29.8k52138
answered Nov 16 '18 at 10:00
jarlhjarlh
29.8k52138
29.8k52138
Thank you, jarlh! :)
– Lo Ri
Nov 16 '18 at 12:19
add a comment |
Thank you, jarlh! :)
– Lo Ri
Nov 16 '18 at 12:19
Thank you, jarlh! :)
– Lo Ri
Nov 16 '18 at 12:19
Thank you, jarlh! :)
– Lo Ri
Nov 16 '18 at 12:19
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%2f53335282%2fhow-to-query-for-unpaired-values-of-a-property%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
Please, provide sample table data and the expected result as formatted text, not images (or links to images.)
– jarlh
Nov 16 '18 at 9:52