Mixing
SQL SELECT query where the IDs were already found
I have 2 tables:
Table A has 3 columns (for example) with opportunity sales header data:
OPP_ID, CLOSE_DTTM, STAGE
Table B has 3 columns with the individual line items for the Opportunities:
OPP_LINE_ID, OPP_ID, AMOUNT_USD
I have a select statement that correctly parses through Table A and returns a list of Opportunities. What I would like to do is, without joining the data, to have a SELECT statement that will get data from Table B but only for the OPP_IDs that were found in my first query.
The result should be 2 views/resultset (one for each select query) and not just 1 combined view where Table B is joined to Table A.
The reason why I want to keep them separate is because I will have to perform a few manipulations to the result from table B and i don't want the result from table A affected.
sql select
edited Jan 2 at 10:24
vishu minhas
1,02211025
asked Jan 2 at 9:16
touyetstouyets
51941229
|
show 1 more comment
I have 2 tables:
Table A has 3 columns (for example) with opportunity sales header data:
OPP_ID, CLOSE_DTTM, STAGE
Table B has 3 columns with the individual line items for the Opportunities:
OPP_LINE_ID, OPP_ID, AMOUNT_USD
I have a select statement that correctly parses through Table A and returns a list of Opportunities. What I would like to do is, without joining the data, to have a SELECT statement that will get data from Table B but only for the OPP_IDs that were found in my first query.
The result should be 2 views/resultset (one for each select query) and not just 1 combined view where Table B is joined to Table A.
The reason why I want to keep them separate is because I will have to perform a few manipulations to the result from table B and i don't want the result from table A affected.
sql select
edited Jan 2 at 10:24
vishu minhas
1,02211025
asked Jan 2 at 9:16
touyetstouyets
51941229
2
could you please add sample data and expected output in table format
– fa06
Jan 2 at 9:17
2
Also show us your current query attempt.
– jarlh
Jan 2 at 9:19
2
Your question doesn't really make sense. This can be done without using the JOIN keyword, but cannot be done without joining (==relating) the data. Manipulating table B in a database does not necessarily manipulate table A; the only scenario where it does is if there is a foreign key relation that cascades updates made to primary keys in B, into the child key columns in A
– Caius Jard
Jan 2 at 9:25
correct the question doesnt make any sense at all
– Himanshu Ahuja
Jan 2 at 9:27
What is your RDBMS? You could use temp table to get your intermediated result set, then useJOIN/ IN..., or duplicate your first query to use in your 2 views...
– Pham X. Bach
Jan 2 at 9:29
|
show 1 more comment
I have 2 tables:
Table A has 3 columns (for example) with opportunity sales header data:
OPP_ID, CLOSE_DTTM, STAGE
Table B has 3 columns with the individual line items for the Opportunities:
OPP_LINE_ID, OPP_ID, AMOUNT_USD
I have a select statement that correctly parses through Table A and returns a list of Opportunities. What I would like to do is, without joining the data, to have a SELECT statement that will get data from Table B but only for the OPP_IDs that were found in my first query.
The result should be 2 views/resultset (one for each select query) and not just 1 combined view where Table B is joined to Table A.
The reason why I want to keep them separate is because I will have to perform a few manipulations to the result from table B and i don't want the result from table A affected.
sql select
edited Jan 2 at 10:24
vishu minhas
1,02211025
asked Jan 2 at 9:16
touyetstouyets
51941229
I have 2 tables:
Table A has 3 columns (for example) with opportunity sales header data:
OPP_ID, CLOSE_DTTM, STAGE
Table B has 3 columns with the individual line items for the Opportunities:
OPP_LINE_ID, OPP_ID, AMOUNT_USD
I have a select statement that correctly parses through Table A and returns a list of Opportunities. What I would like to do is, without joining the data, to have a SELECT statement that will get data from Table B but only for the OPP_IDs that were found in my first query.
The result should be 2 views/resultset (one for each select query) and not just 1 combined view where Table B is joined to Table A.
The reason why I want to keep them separate is because I will have to perform a few manipulations to the result from table B and i don't want the result from table A affected.
sql select
sql select
edited Jan 2 at 10:24
vishu minhas
1,02211025
asked Jan 2 at 9:16
touyetstouyets
51941229
edited Jan 2 at 10:24
vishu minhas
1,02211025
asked Jan 2 at 9:16
touyetstouyets
51941229
edited Jan 2 at 10:24
vishu minhas
1,02211025
edited Jan 2 at 10:24
vishu minhas
1,02211025
edited Jan 2 at 10:24
vishu minhas
1,02211025
1,02211025
asked Jan 2 at 9:16
touyetstouyets
51941229
asked Jan 2 at 9:16
touyetstouyets
51941229
asked Jan 2 at 9:16
touyetstouyets
51941229
51941229
2
could you please add sample data and expected output in table format
– fa06
Jan 2 at 9:17
2
Also show us your current query attempt.
– jarlh
Jan 2 at 9:19
2
Your question doesn't really make sense. This can be done without using the JOIN keyword, but cannot be done without joining (==relating) the data. Manipulating table B in a database does not necessarily manipulate table A; the only scenario where it does is if there is a foreign key relation that cascades updates made to primary keys in B, into the child key columns in A
– Caius Jard
Jan 2 at 9:25
correct the question doesnt make any sense at all
– Himanshu Ahuja
Jan 2 at 9:27
What is your RDBMS? You could use temp table to get your intermediated result set, then useJOIN/ IN..., or duplicate your first query to use in your 2 views...
– Pham X. Bach
Jan 2 at 9:29
|
show 1 more comment
2
could you please add sample data and expected output in table format
– fa06
Jan 2 at 9:17
2
Also show us your current query attempt.
– jarlh
Jan 2 at 9:19
2
Your question doesn't really make sense. This can be done without using the JOIN keyword, but cannot be done without joining (==relating) the data. Manipulating table B in a database does not necessarily manipulate table A; the only scenario where it does is if there is a foreign key relation that cascades updates made to primary keys in B, into the child key columns in A
– Caius Jard
Jan 2 at 9:25
correct the question doesnt make any sense at all
– Himanshu Ahuja
Jan 2 at 9:27
What is your RDBMS? You could use temp table to get your intermediated result set, then useJOIN/ IN..., or duplicate your first query to use in your 2 views...
– Pham X. Bach
Jan 2 at 9:29
2
2
could you please add sample data and expected output in table format
– fa06
Jan 2 at 9:17
could you please add sample data and expected output in table format
– fa06
Jan 2 at 9:17
2
2
Also show us your current query attempt.
– jarlh
Jan 2 at 9:19
Also show us your current query attempt.
– jarlh
Jan 2 at 9:19
2
2
Your question doesn't really make sense. This can be done without using the JOIN keyword, but cannot be done without joining (==relating) the data. Manipulating table B in a database does not necessarily manipulate table A; the only scenario where it does is if there is a foreign key relation that cascades updates made to primary keys in B, into the child key columns in A
– Caius Jard
Jan 2 at 9:25
Your question doesn't really make sense. This can be done without using the JOIN keyword, but cannot be done without joining (==relating) the data. Manipulating table B in a database does not necessarily manipulate table A; the only scenario where it does is if there is a foreign key relation that cascades updates made to primary keys in B, into the child key columns in A
– Caius Jard
Jan 2 at 9:25
correct the question doesnt make any sense at all
– Himanshu Ahuja
Jan 2 at 9:27
correct the question doesnt make any sense at all
– Himanshu Ahuja
Jan 2 at 9:27
What is your RDBMS? You could use temp table to get your intermediated result set, then use
JOIN/ IN..., or duplicate your first query to use in your 2 views...– Pham X. Bach
Jan 2 at 9:29
What is your RDBMS? You could use temp table to get your intermediated result set, then use
JOIN/ IN..., or duplicate your first query to use in your 2 views...– Pham X. Bach
Jan 2 at 9:29
|
show 1 more comment
3 Answers
3
active
oldest
votes
Subquery is all what you need
SELECT OPP_ID, CLOSE_DTTM, STAGE
From table a
where a.opp_id IN (Select opp_id from table b)
answered Jan 2 at 9:25
Himanshu AhujaHimanshu Ahuja
9322218
add a comment |
Presuming you're using this in some client side data access library that represents B's data in some 2 dimensional collection and you want to manipulate it without affecting/ having A's data present in that collection:
Identify the records in A:
SELECT * FROM a WHERE somecolumn = 'somevalue'
Identify the records in B that relate to A, but don't return A's data:
SELECT b.* FROM a JOIN b ON a.opp_id = b.opp_id WHERE a.somecolumn = 'somevalue'
Just because JOIN is used doesn't mean your end-consuming program has to know about A's data. You could also use IN, like the other answer does, but internally the database will rewrite them to be the same thing anyway
answered Jan 2 at 9:30
Caius JardCaius Jard
12.5k21340
add a comment |
I tend to use exists for this type of query:
select b.*
from b
where exists (select 1 from a where a.opp_id = b.opp_id);
If you want two results sets, you need to run two queries. It is unclear what the second query is, perhaps the first query on A.
answered Jan 2 at 12:11
Gordon LinoffGordon Linoff
789k35314418
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%2f54003785%2fsql-select-query-where-the-ids-were-already-found%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
Subquery is all what you need
SELECT OPP_ID, CLOSE_DTTM, STAGE
From table a
where a.opp_id IN (Select opp_id from table b)
answered Jan 2 at 9:25
Himanshu AhujaHimanshu Ahuja
9322218
add a comment |
Subquery is all what you need
SELECT OPP_ID, CLOSE_DTTM, STAGE
From table a
where a.opp_id IN (Select opp_id from table b)
answered Jan 2 at 9:25
Himanshu AhujaHimanshu Ahuja
9322218
add a comment |
Subquery is all what you need
SELECT OPP_ID, CLOSE_DTTM, STAGE
From table a
where a.opp_id IN (Select opp_id from table b)
answered Jan 2 at 9:25
Himanshu AhujaHimanshu Ahuja
9322218
Subquery is all what you need
SELECT OPP_ID, CLOSE_DTTM, STAGE
From table a
where a.opp_id IN (Select opp_id from table b)
answered Jan 2 at 9:25
Himanshu AhujaHimanshu Ahuja
9322218
answered Jan 2 at 9:25
Himanshu AhujaHimanshu Ahuja
9322218
answered Jan 2 at 9:25
Himanshu AhujaHimanshu Ahuja
9322218
answered Jan 2 at 9:25
Himanshu AhujaHimanshu Ahuja
9322218
9322218
add a comment |
add a comment |
Presuming you're using this in some client side data access library that represents B's data in some 2 dimensional collection and you want to manipulate it without affecting/ having A's data present in that collection:
Identify the records in A:
SELECT * FROM a WHERE somecolumn = 'somevalue'
Identify the records in B that relate to A, but don't return A's data:
SELECT b.* FROM a JOIN b ON a.opp_id = b.opp_id WHERE a.somecolumn = 'somevalue'
Just because JOIN is used doesn't mean your end-consuming program has to know about A's data. You could also use IN, like the other answer does, but internally the database will rewrite them to be the same thing anyway
answered Jan 2 at 9:30
Caius JardCaius Jard
12.5k21340
add a comment |
Presuming you're using this in some client side data access library that represents B's data in some 2 dimensional collection and you want to manipulate it without affecting/ having A's data present in that collection:
Identify the records in A:
SELECT * FROM a WHERE somecolumn = 'somevalue'
Identify the records in B that relate to A, but don't return A's data:
SELECT b.* FROM a JOIN b ON a.opp_id = b.opp_id WHERE a.somecolumn = 'somevalue'
Just because JOIN is used doesn't mean your end-consuming program has to know about A's data. You could also use IN, like the other answer does, but internally the database will rewrite them to be the same thing anyway
answered Jan 2 at 9:30
Caius JardCaius Jard
12.5k21340
add a comment |
Presuming you're using this in some client side data access library that represents B's data in some 2 dimensional collection and you want to manipulate it without affecting/ having A's data present in that collection:
Identify the records in A:
SELECT * FROM a WHERE somecolumn = 'somevalue'
Identify the records in B that relate to A, but don't return A's data:
SELECT b.* FROM a JOIN b ON a.opp_id = b.opp_id WHERE a.somecolumn = 'somevalue'
Just because JOIN is used doesn't mean your end-consuming program has to know about A's data. You could also use IN, like the other answer does, but internally the database will rewrite them to be the same thing anyway
answered Jan 2 at 9:30
Caius JardCaius Jard
12.5k21340
Presuming you're using this in some client side data access library that represents B's data in some 2 dimensional collection and you want to manipulate it without affecting/ having A's data present in that collection:
Identify the records in A:
SELECT * FROM a WHERE somecolumn = 'somevalue'
Identify the records in B that relate to A, but don't return A's data:
SELECT b.* FROM a JOIN b ON a.opp_id = b.opp_id WHERE a.somecolumn = 'somevalue'
Just because JOIN is used doesn't mean your end-consuming program has to know about A's data. You could also use IN, like the other answer does, but internally the database will rewrite them to be the same thing anyway
answered Jan 2 at 9:30
Caius JardCaius Jard
12.5k21340
answered Jan 2 at 9:30
Caius JardCaius Jard
12.5k21340
answered Jan 2 at 9:30
Caius JardCaius Jard
12.5k21340
answered Jan 2 at 9:30
Caius JardCaius Jard
12.5k21340
12.5k21340
add a comment |
add a comment |
I tend to use exists for this type of query:
select b.*
from b
where exists (select 1 from a where a.opp_id = b.opp_id);
If you want two results sets, you need to run two queries. It is unclear what the second query is, perhaps the first query on A.
answered Jan 2 at 12:11
Gordon LinoffGordon Linoff
789k35314418
add a comment |
I tend to use exists for this type of query:
select b.*
from b
where exists (select 1 from a where a.opp_id = b.opp_id);
If you want two results sets, you need to run two queries. It is unclear what the second query is, perhaps the first query on A.
answered Jan 2 at 12:11
Gordon LinoffGordon Linoff
789k35314418
add a comment |
I tend to use exists for this type of query:
select b.*
from b
where exists (select 1 from a where a.opp_id = b.opp_id);
If you want two results sets, you need to run two queries. It is unclear what the second query is, perhaps the first query on A.
answered Jan 2 at 12:11
Gordon LinoffGordon Linoff
789k35314418
I tend to use exists for this type of query:
select b.*
from b
where exists (select 1 from a where a.opp_id = b.opp_id);
If you want two results sets, you need to run two queries. It is unclear what the second query is, perhaps the first query on A.
answered Jan 2 at 12:11
Gordon LinoffGordon Linoff
789k35314418
answered Jan 2 at 12:11
Gordon LinoffGordon Linoff
789k35314418
answered Jan 2 at 12:11
Gordon LinoffGordon Linoff
789k35314418
answered Jan 2 at 12:11
Gordon LinoffGordon Linoff
789k35314418
789k35314418
add a comment |
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%2f54003785%2fsql-select-query-where-the-ids-were-already-found%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
2
could you please add sample data and expected output in table format
– fa06
Jan 2 at 9:17
2
Also show us your current query attempt.
– jarlh
Jan 2 at 9:19
2
Your question doesn't really make sense. This can be done without using the JOIN keyword, but cannot be done without joining (==relating) the data. Manipulating table B in a database does not necessarily manipulate table A; the only scenario where it does is if there is a foreign key relation that cascades updates made to primary keys in B, into the child key columns in A
– Caius Jard
Jan 2 at 9:25
correct the question doesnt make any sense at all
– Himanshu Ahuja
Jan 2 at 9:27
What is your RDBMS? You could use temp table to get your intermediated result set, then use
JOIN/ IN..., or duplicate your first query to use in your 2 views...– Pham X. Bach
Jan 2 at 9:29