Mixing
Easy way to get a single resultset from three identical tables?
The DB I'm working with has three tables with identical column layouts, OPEX, NOPEX and CAPEX. I would like to query all three for items with a matching AssetId and get a single result set so that I can process them all at the same time in my .Net code.
The twist is that I do need to know which table they came from.
I know I can do this with a series of CASE in the SELECT clause, perhaps using the ID column in each where it's non-zero to decide which of the tables it came from. But I would have to have one for each column and the tables are pretty wide.
Is there some other way to solve this problem?
sql
sql-server asked Nov 20 '18 at 16:42
Maury MarkowitzMaury Markowitz
3,78062863
add a comment |
The DB I'm working with has three tables with identical column layouts, OPEX, NOPEX and CAPEX. I would like to query all three for items with a matching AssetId and get a single result set so that I can process them all at the same time in my .Net code.
The twist is that I do need to know which table they came from.
I know I can do this with a series of CASE in the SELECT clause, perhaps using the ID column in each where it's non-zero to decide which of the tables it came from. But I would have to have one for each column and the tables are pretty wide.
Is there some other way to solve this problem?
sql
sql-server asked Nov 20 '18 at 16:42
Maury MarkowitzMaury Markowitz
3,78062863
2
Select 'O' as flag, * from OPEX UNION ALL SELECT 'N' as flag, * FROM NOPEXX UNION ALL...etc
– Cato
Nov 20 '18 at 16:44
well that was fast!
– Maury Markowitz
Nov 20 '18 at 16:45
@Cato doesn't satisfy requirement for matching AssetID
– TJB
Nov 20 '18 at 16:46
1
@TJB, yeah I know, but it's just to give a general idea, I'd expect someone to be able to apply their WHERE clauses as needed
– Cato
Nov 20 '18 at 16:54
add a comment |
The DB I'm working with has three tables with identical column layouts, OPEX, NOPEX and CAPEX. I would like to query all three for items with a matching AssetId and get a single result set so that I can process them all at the same time in my .Net code.
The twist is that I do need to know which table they came from.
I know I can do this with a series of CASE in the SELECT clause, perhaps using the ID column in each where it's non-zero to decide which of the tables it came from. But I would have to have one for each column and the tables are pretty wide.
Is there some other way to solve this problem?
sql
sql-server asked Nov 20 '18 at 16:42
Maury MarkowitzMaury Markowitz
3,78062863
The DB I'm working with has three tables with identical column layouts, OPEX, NOPEX and CAPEX. I would like to query all three for items with a matching AssetId and get a single result set so that I can process them all at the same time in my .Net code.
The twist is that I do need to know which table they came from.
I know I can do this with a series of CASE in the SELECT clause, perhaps using the ID column in each where it's non-zero to decide which of the tables it came from. But I would have to have one for each column and the tables are pretty wide.
Is there some other way to solve this problem?
sql
sql-server sql
sql-server asked Nov 20 '18 at 16:42
Maury MarkowitzMaury Markowitz
3,78062863
asked Nov 20 '18 at 16:42
Maury MarkowitzMaury Markowitz
3,78062863
asked Nov 20 '18 at 16:42
Maury MarkowitzMaury Markowitz
3,78062863
asked Nov 20 '18 at 16:42
Maury MarkowitzMaury Markowitz
3,78062863
asked Nov 20 '18 at 16:42
Maury MarkowitzMaury Markowitz
3,78062863
3,78062863
2
Select 'O' as flag, * from OPEX UNION ALL SELECT 'N' as flag, * FROM NOPEXX UNION ALL...etc
– Cato
Nov 20 '18 at 16:44
well that was fast!
– Maury Markowitz
Nov 20 '18 at 16:45
@Cato doesn't satisfy requirement for matching AssetID
– TJB
Nov 20 '18 at 16:46
1
@TJB, yeah I know, but it's just to give a general idea, I'd expect someone to be able to apply their WHERE clauses as needed
– Cato
Nov 20 '18 at 16:54
add a comment |
2
Select 'O' as flag, * from OPEX UNION ALL SELECT 'N' as flag, * FROM NOPEXX UNION ALL...etc
– Cato
Nov 20 '18 at 16:44
well that was fast!
– Maury Markowitz
Nov 20 '18 at 16:45
@Cato doesn't satisfy requirement for matching AssetID
– TJB
Nov 20 '18 at 16:46
1
@TJB, yeah I know, but it's just to give a general idea, I'd expect someone to be able to apply their WHERE clauses as needed
– Cato
Nov 20 '18 at 16:54
2
2
Select 'O' as flag, * from OPEX UNION ALL SELECT 'N' as flag, * FROM NOPEXX UNION ALL...etc
– Cato
Nov 20 '18 at 16:44
Select 'O' as flag, * from OPEX UNION ALL SELECT 'N' as flag, * FROM NOPEXX UNION ALL...etc
– Cato
Nov 20 '18 at 16:44
well that was fast!
– Maury Markowitz
Nov 20 '18 at 16:45
well that was fast!
– Maury Markowitz
Nov 20 '18 at 16:45
@Cato doesn't satisfy requirement for matching AssetID
– TJB
Nov 20 '18 at 16:46
@Cato doesn't satisfy requirement for matching AssetID
– TJB
Nov 20 '18 at 16:46
1
1
@TJB, yeah I know, but it's just to give a general idea, I'd expect someone to be able to apply their WHERE clauses as needed
– Cato
Nov 20 '18 at 16:54
@TJB, yeah I know, but it's just to give a general idea, I'd expect someone to be able to apply their WHERE clauses as needed
– Cato
Nov 20 '18 at 16:54
add a comment |
2 Answers
2
active
oldest
votes
In order to get them into one set, you would use a combination of UNION and EXISTS() checks. The UNION ALL will give you a single result set that contains data from all three tables, and the EXISTS check on each will confirm the table you are querying from has corresponding records in the other tables.
SELECT *, 'OPEX' AS table_name
FROM OPEX o
WHERE EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = o.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = o.asset_id)
UNION ALL
SELECT *, 'NOPEX' AS table_name
FROM NOPEX n
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = n.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = n.asset_id)
UNION ALL
SELECT *, 'CAPEX' AS table_name
FROM CAPEX c
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = c.asset_id)
AND EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = c.asset_id)
I guess you could also do INNER JOINs?
SELECT c.*, 'CAPEX' AS table_name
FROM CAPEX c
INNER JOIN OPEX o
ON o.asset_id = c.asset_id
INNER JOIN NOPEX n
ON n.asset_id = c.asset_id
UNION ALL
SELECT o.*, 'OPEX' AS table_name
FROM OPEX o
INNER JOIN CAPEX c
ON c.asset_id = o.asset_id
INNER JOIN NOPEX n
ON n.asset_id = o.asset_id
UNION ALL
SELECT n.*, 'NOPEX' AS table_name
FROM NOPEX n
INNER JOIN OPEX o
ON o.asset_id = n.asset_id
INNER JOIN CAPEX c
ON c.asset_id = n.asset_id
answered Nov 20 '18 at 16:50
dfundakodfundako
4,9733922
I used the union and flag column, which worked perfectly. The tables in question are small enough that the performance is not going to be an issue.
– Maury Markowitz
Nov 20 '18 at 17:10
add a comment |
Similar answer to dfundako, but resolving sooner where AssetId is in all three tables and less hitting of the indexes on the related tables:
;with cte as (
select
AssetID
from (
select distinct
AssetID
from Opex
union all
select distinct
AssetID
from Nopex
union all
select distinct
AssetID
from Capex
) as AssetIDs
group by AssetId
having count(AssetId) = 3
)
select 'Opex', * from Opex as o
inner join cte
on o.AssetID = cte.AssetID
union all
select 'Nopex', * from Nopex as n
inner join cte
on n.AssetID = cte.AssetID
union all
select 'Capex', * from Capex as c
inner join cte
on c.AssetID = cte.AssetID
answered Nov 20 '18 at 17:04
TJBTJB
543314
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%2f53397635%2feasy-way-to-get-a-single-resultset-from-three-identical-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
In order to get them into one set, you would use a combination of UNION and EXISTS() checks. The UNION ALL will give you a single result set that contains data from all three tables, and the EXISTS check on each will confirm the table you are querying from has corresponding records in the other tables.
SELECT *, 'OPEX' AS table_name
FROM OPEX o
WHERE EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = o.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = o.asset_id)
UNION ALL
SELECT *, 'NOPEX' AS table_name
FROM NOPEX n
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = n.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = n.asset_id)
UNION ALL
SELECT *, 'CAPEX' AS table_name
FROM CAPEX c
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = c.asset_id)
AND EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = c.asset_id)
I guess you could also do INNER JOINs?
SELECT c.*, 'CAPEX' AS table_name
FROM CAPEX c
INNER JOIN OPEX o
ON o.asset_id = c.asset_id
INNER JOIN NOPEX n
ON n.asset_id = c.asset_id
UNION ALL
SELECT o.*, 'OPEX' AS table_name
FROM OPEX o
INNER JOIN CAPEX c
ON c.asset_id = o.asset_id
INNER JOIN NOPEX n
ON n.asset_id = o.asset_id
UNION ALL
SELECT n.*, 'NOPEX' AS table_name
FROM NOPEX n
INNER JOIN OPEX o
ON o.asset_id = n.asset_id
INNER JOIN CAPEX c
ON c.asset_id = n.asset_id
answered Nov 20 '18 at 16:50
dfundakodfundako
4,9733922
I used the union and flag column, which worked perfectly. The tables in question are small enough that the performance is not going to be an issue.
– Maury Markowitz
Nov 20 '18 at 17:10
add a comment |
In order to get them into one set, you would use a combination of UNION and EXISTS() checks. The UNION ALL will give you a single result set that contains data from all three tables, and the EXISTS check on each will confirm the table you are querying from has corresponding records in the other tables.
SELECT *, 'OPEX' AS table_name
FROM OPEX o
WHERE EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = o.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = o.asset_id)
UNION ALL
SELECT *, 'NOPEX' AS table_name
FROM NOPEX n
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = n.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = n.asset_id)
UNION ALL
SELECT *, 'CAPEX' AS table_name
FROM CAPEX c
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = c.asset_id)
AND EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = c.asset_id)
I guess you could also do INNER JOINs?
SELECT c.*, 'CAPEX' AS table_name
FROM CAPEX c
INNER JOIN OPEX o
ON o.asset_id = c.asset_id
INNER JOIN NOPEX n
ON n.asset_id = c.asset_id
UNION ALL
SELECT o.*, 'OPEX' AS table_name
FROM OPEX o
INNER JOIN CAPEX c
ON c.asset_id = o.asset_id
INNER JOIN NOPEX n
ON n.asset_id = o.asset_id
UNION ALL
SELECT n.*, 'NOPEX' AS table_name
FROM NOPEX n
INNER JOIN OPEX o
ON o.asset_id = n.asset_id
INNER JOIN CAPEX c
ON c.asset_id = n.asset_id
answered Nov 20 '18 at 16:50
dfundakodfundako
4,9733922
I used the union and flag column, which worked perfectly. The tables in question are small enough that the performance is not going to be an issue.
– Maury Markowitz
Nov 20 '18 at 17:10
add a comment |
In order to get them into one set, you would use a combination of UNION and EXISTS() checks. The UNION ALL will give you a single result set that contains data from all three tables, and the EXISTS check on each will confirm the table you are querying from has corresponding records in the other tables.
SELECT *, 'OPEX' AS table_name
FROM OPEX o
WHERE EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = o.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = o.asset_id)
UNION ALL
SELECT *, 'NOPEX' AS table_name
FROM NOPEX n
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = n.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = n.asset_id)
UNION ALL
SELECT *, 'CAPEX' AS table_name
FROM CAPEX c
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = c.asset_id)
AND EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = c.asset_id)
I guess you could also do INNER JOINs?
SELECT c.*, 'CAPEX' AS table_name
FROM CAPEX c
INNER JOIN OPEX o
ON o.asset_id = c.asset_id
INNER JOIN NOPEX n
ON n.asset_id = c.asset_id
UNION ALL
SELECT o.*, 'OPEX' AS table_name
FROM OPEX o
INNER JOIN CAPEX c
ON c.asset_id = o.asset_id
INNER JOIN NOPEX n
ON n.asset_id = o.asset_id
UNION ALL
SELECT n.*, 'NOPEX' AS table_name
FROM NOPEX n
INNER JOIN OPEX o
ON o.asset_id = n.asset_id
INNER JOIN CAPEX c
ON c.asset_id = n.asset_id
answered Nov 20 '18 at 16:50
dfundakodfundako
4,9733922
In order to get them into one set, you would use a combination of UNION and EXISTS() checks. The UNION ALL will give you a single result set that contains data from all three tables, and the EXISTS check on each will confirm the table you are querying from has corresponding records in the other tables.
SELECT *, 'OPEX' AS table_name
FROM OPEX o
WHERE EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = o.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = o.asset_id)
UNION ALL
SELECT *, 'NOPEX' AS table_name
FROM NOPEX n
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = n.asset_id)
AND EXISTS (
SELECT 1
FROM CAPEX c
WHERE c.asset_id = n.asset_id)
UNION ALL
SELECT *, 'CAPEX' AS table_name
FROM CAPEX c
WHERE EXISTS (
SELECT 1
FROM Opex o
WHERE o.asset_id = c.asset_id)
AND EXISTS (
SELECT 1
FROM NOPEX n
WHERE n.asset_id = c.asset_id)
I guess you could also do INNER JOINs?
SELECT c.*, 'CAPEX' AS table_name
FROM CAPEX c
INNER JOIN OPEX o
ON o.asset_id = c.asset_id
INNER JOIN NOPEX n
ON n.asset_id = c.asset_id
UNION ALL
SELECT o.*, 'OPEX' AS table_name
FROM OPEX o
INNER JOIN CAPEX c
ON c.asset_id = o.asset_id
INNER JOIN NOPEX n
ON n.asset_id = o.asset_id
UNION ALL
SELECT n.*, 'NOPEX' AS table_name
FROM NOPEX n
INNER JOIN OPEX o
ON o.asset_id = n.asset_id
INNER JOIN CAPEX c
ON c.asset_id = n.asset_id
answered Nov 20 '18 at 16:50
dfundakodfundako
4,9733922
edited Nov 20 '18 at 16:56
answered Nov 20 '18 at 16:50
dfundakodfundako
4,9733922
answered Nov 20 '18 at 16:50
dfundakodfundako
4,9733922
answered Nov 20 '18 at 16:50
dfundakodfundako
4,9733922
4,9733922
I used the union and flag column, which worked perfectly. The tables in question are small enough that the performance is not going to be an issue.
– Maury Markowitz
Nov 20 '18 at 17:10
add a comment |
I used the union and flag column, which worked perfectly. The tables in question are small enough that the performance is not going to be an issue.
– Maury Markowitz
Nov 20 '18 at 17:10
I used the union and flag column, which worked perfectly. The tables in question are small enough that the performance is not going to be an issue.
– Maury Markowitz
Nov 20 '18 at 17:10
I used the union and flag column, which worked perfectly. The tables in question are small enough that the performance is not going to be an issue.
– Maury Markowitz
Nov 20 '18 at 17:10
add a comment |
Similar answer to dfundako, but resolving sooner where AssetId is in all three tables and less hitting of the indexes on the related tables:
;with cte as (
select
AssetID
from (
select distinct
AssetID
from Opex
union all
select distinct
AssetID
from Nopex
union all
select distinct
AssetID
from Capex
) as AssetIDs
group by AssetId
having count(AssetId) = 3
)
select 'Opex', * from Opex as o
inner join cte
on o.AssetID = cte.AssetID
union all
select 'Nopex', * from Nopex as n
inner join cte
on n.AssetID = cte.AssetID
union all
select 'Capex', * from Capex as c
inner join cte
on c.AssetID = cte.AssetID
answered Nov 20 '18 at 17:04
TJBTJB
543314
add a comment |
Similar answer to dfundako, but resolving sooner where AssetId is in all three tables and less hitting of the indexes on the related tables:
;with cte as (
select
AssetID
from (
select distinct
AssetID
from Opex
union all
select distinct
AssetID
from Nopex
union all
select distinct
AssetID
from Capex
) as AssetIDs
group by AssetId
having count(AssetId) = 3
)
select 'Opex', * from Opex as o
inner join cte
on o.AssetID = cte.AssetID
union all
select 'Nopex', * from Nopex as n
inner join cte
on n.AssetID = cte.AssetID
union all
select 'Capex', * from Capex as c
inner join cte
on c.AssetID = cte.AssetID
answered Nov 20 '18 at 17:04
TJBTJB
543314
add a comment |
Similar answer to dfundako, but resolving sooner where AssetId is in all three tables and less hitting of the indexes on the related tables:
;with cte as (
select
AssetID
from (
select distinct
AssetID
from Opex
union all
select distinct
AssetID
from Nopex
union all
select distinct
AssetID
from Capex
) as AssetIDs
group by AssetId
having count(AssetId) = 3
)
select 'Opex', * from Opex as o
inner join cte
on o.AssetID = cte.AssetID
union all
select 'Nopex', * from Nopex as n
inner join cte
on n.AssetID = cte.AssetID
union all
select 'Capex', * from Capex as c
inner join cte
on c.AssetID = cte.AssetID
answered Nov 20 '18 at 17:04
TJBTJB
543314
Similar answer to dfundako, but resolving sooner where AssetId is in all three tables and less hitting of the indexes on the related tables:
;with cte as (
select
AssetID
from (
select distinct
AssetID
from Opex
union all
select distinct
AssetID
from Nopex
union all
select distinct
AssetID
from Capex
) as AssetIDs
group by AssetId
having count(AssetId) = 3
)
select 'Opex', * from Opex as o
inner join cte
on o.AssetID = cte.AssetID
union all
select 'Nopex', * from Nopex as n
inner join cte
on n.AssetID = cte.AssetID
union all
select 'Capex', * from Capex as c
inner join cte
on c.AssetID = cte.AssetID
answered Nov 20 '18 at 17:04
TJBTJB
543314
answered Nov 20 '18 at 17:04
TJBTJB
543314
answered Nov 20 '18 at 17:04
TJBTJB
543314
answered Nov 20 '18 at 17:04
TJBTJB
543314
543314
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%2f53397635%2feasy-way-to-get-a-single-resultset-from-three-identical-tables%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
Select 'O' as flag, * from OPEX UNION ALL SELECT 'N' as flag, * FROM NOPEXX UNION ALL...etc
– Cato
Nov 20 '18 at 16:44
well that was fast!
– Maury Markowitz
Nov 20 '18 at 16:45
@Cato doesn't satisfy requirement for matching AssetID
– TJB
Nov 20 '18 at 16:46
1
@TJB, yeah I know, but it's just to give a general idea, I'd expect someone to be able to apply their WHERE clauses as needed
– Cato
Nov 20 '18 at 16:54