Join two tables and create a new one in SQL
I am new to SQL and trying to apply a concept of Joins in MS-access.
I have two tables Table 1 and Table 2:
I want a query which will create a Table 3:
Ideally i want all the data from both tables but wherever the ID's are matching in both tables, the category should become '3'
Can someone please help me with this query. Thanks in advance.
I have attached the samples for your reference
ms-access
edited Sep 28 '16 at 9:33
ypercubeᵀᴹ
77.8k11136219
asked Sep 28 '16 at 9:27
user106875user106875
61
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I am new to SQL and trying to apply a concept of Joins in MS-access.
I have two tables Table 1 and Table 2:
I want a query which will create a Table 3:
Ideally i want all the data from both tables but wherever the ID's are matching in both tables, the category should become '3'
Can someone please help me with this query. Thanks in advance.
I have attached the samples for your reference
ms-access
edited Sep 28 '16 at 9:33
ypercubeᵀᴹ
77.8k11136219
asked Sep 28 '16 at 9:27
user106875user106875
61
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What DBMS do you use? SQL Server, Postgres, Oracle, DB2, SQLite, Firebird, etc ...? And what if some rows match theIDbut have different values in `Name?
– ypercubeᵀᴹ
Sep 28 '16 at 9:32
Will the categories in the source tables necessarily be 1 and 2? What if they are other values? What if one of them is already 3 and there's a match with a different category for the sameID,Namein the other table?
– Andriy M
Sep 29 '16 at 6:06
add a comment |
I am new to SQL and trying to apply a concept of Joins in MS-access.
I have two tables Table 1 and Table 2:
I want a query which will create a Table 3:
Ideally i want all the data from both tables but wherever the ID's are matching in both tables, the category should become '3'
Can someone please help me with this query. Thanks in advance.
I have attached the samples for your reference
ms-access
edited Sep 28 '16 at 9:33
ypercubeᵀᴹ
77.8k11136219
asked Sep 28 '16 at 9:27
user106875user106875
61
I am new to SQL and trying to apply a concept of Joins in MS-access.
I have two tables Table 1 and Table 2:
I want a query which will create a Table 3:
Ideally i want all the data from both tables but wherever the ID's are matching in both tables, the category should become '3'
Can someone please help me with this query. Thanks in advance.
I have attached the samples for your reference
ms-access
ms-access
edited Sep 28 '16 at 9:33
ypercubeᵀᴹ
77.8k11136219
asked Sep 28 '16 at 9:27
user106875user106875
61
edited Sep 28 '16 at 9:33
ypercubeᵀᴹ
77.8k11136219
asked Sep 28 '16 at 9:27
user106875user106875
61
edited Sep 28 '16 at 9:33
ypercubeᵀᴹ
77.8k11136219
edited Sep 28 '16 at 9:33
ypercubeᵀᴹ
77.8k11136219
edited Sep 28 '16 at 9:33
ypercubeᵀᴹ
77.8k11136219
77.8k11136219
asked Sep 28 '16 at 9:27
user106875user106875
61
asked Sep 28 '16 at 9:27
user106875user106875
61
asked Sep 28 '16 at 9:27
user106875user106875
61
61
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What DBMS do you use? SQL Server, Postgres, Oracle, DB2, SQLite, Firebird, etc ...? And what if some rows match theIDbut have different values in `Name?
– ypercubeᵀᴹ
Sep 28 '16 at 9:32
Will the categories in the source tables necessarily be 1 and 2? What if they are other values? What if one of them is already 3 and there's a match with a different category for the sameID,Namein the other table?
– Andriy M
Sep 29 '16 at 6:06
add a comment |
What DBMS do you use? SQL Server, Postgres, Oracle, DB2, SQLite, Firebird, etc ...? And what if some rows match theIDbut have different values in `Name?
– ypercubeᵀᴹ
Sep 28 '16 at 9:32
Will the categories in the source tables necessarily be 1 and 2? What if they are other values? What if one of them is already 3 and there's a match with a different category for the sameID,Namein the other table?
– Andriy M
Sep 29 '16 at 6:06
What DBMS do you use? SQL Server, Postgres, Oracle, DB2, SQLite, Firebird, etc ...? And what if some rows match the
ID but have different values in `Name?– ypercubeᵀᴹ
Sep 28 '16 at 9:32
What DBMS do you use? SQL Server, Postgres, Oracle, DB2, SQLite, Firebird, etc ...? And what if some rows match the
ID but have different values in `Name?– ypercubeᵀᴹ
Sep 28 '16 at 9:32
Will the categories in the source tables necessarily be 1 and 2? What if they are other values? What if one of them is already 3 and there's a match with a different category for the same
ID,Name in the other table?– Andriy M
Sep 29 '16 at 6:06
Will the categories in the source tables necessarily be 1 and 2? What if they are other values? What if one of them is already 3 and there's a match with a different category for the same
ID,Name in the other table?– Andriy M
Sep 29 '16 at 6:06
add a comment |
1 Answer
1
active
oldest
votes
- You need to
UNION ALLthe two table entries - Then take the
SUM(Category)byGROUP BY ID, Name
So the query will be
SELECT ID, Name, SUM(Category) AS Category
FROM (
SELECT ID, Name, Category FROM Table1
UNION ALL
SELECT ID, Name, Category FROM Table2
) GROUP BY ID, Name
You can insert the entries into the third table.
answered Sep 28 '16 at 9:53
ArulkumarArulkumar
858417
add a comment |
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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%2fdba.stackexchange.com%2fquestions%2f150850%2fjoin-two-tables-and-create-a-new-one-in-sql%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 need to
UNION ALLthe two table entries - Then take the
SUM(Category)byGROUP BY ID, Name
So the query will be
SELECT ID, Name, SUM(Category) AS Category
FROM (
SELECT ID, Name, Category FROM Table1
UNION ALL
SELECT ID, Name, Category FROM Table2
) GROUP BY ID, Name
You can insert the entries into the third table.
answered Sep 28 '16 at 9:53
ArulkumarArulkumar
858417
add a comment |
- You need to
UNION ALLthe two table entries - Then take the
SUM(Category)byGROUP BY ID, Name
So the query will be
SELECT ID, Name, SUM(Category) AS Category
FROM (
SELECT ID, Name, Category FROM Table1
UNION ALL
SELECT ID, Name, Category FROM Table2
) GROUP BY ID, Name
You can insert the entries into the third table.
answered Sep 28 '16 at 9:53
ArulkumarArulkumar
858417
add a comment |
- You need to
UNION ALLthe two table entries - Then take the
SUM(Category)byGROUP BY ID, Name
So the query will be
SELECT ID, Name, SUM(Category) AS Category
FROM (
SELECT ID, Name, Category FROM Table1
UNION ALL
SELECT ID, Name, Category FROM Table2
) GROUP BY ID, Name
You can insert the entries into the third table.
answered Sep 28 '16 at 9:53
ArulkumarArulkumar
858417
- You need to
UNION ALLthe two table entries - Then take the
SUM(Category)byGROUP BY ID, Name
So the query will be
SELECT ID, Name, SUM(Category) AS Category
FROM (
SELECT ID, Name, Category FROM Table1
UNION ALL
SELECT ID, Name, Category FROM Table2
) GROUP BY ID, Name
You can insert the entries into the third table.
answered Sep 28 '16 at 9:53
ArulkumarArulkumar
858417
answered Sep 28 '16 at 9:53
ArulkumarArulkumar
858417
answered Sep 28 '16 at 9:53
ArulkumarArulkumar
858417
answered Sep 28 '16 at 9:53
ArulkumarArulkumar
858417
858417
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators 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%2fdba.stackexchange.com%2fquestions%2f150850%2fjoin-two-tables-and-create-a-new-one-in-sql%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
What DBMS do you use? SQL Server, Postgres, Oracle, DB2, SQLite, Firebird, etc ...? And what if some rows match the
IDbut have different values in `Name?– ypercubeᵀᴹ
Sep 28 '16 at 9:32
Will the categories in the source tables necessarily be 1 and 2? What if they are other values? What if one of them is already 3 and there's a match with a different category for the same
ID,Namein the other table?– Andriy M
Sep 29 '16 at 6:06