Sfrgttk

Film where the government was corrupt with aliens, people sent to kill aliens are given rigged visors not showing the right aliens

Help/tips for a first time writer?

Is it correct to say moon starry nights?

Why is information "lost" when it got into a black hole?

Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?

Airplane gently rocking its wings during whole flight

Does higher Oxidation/ reduction potential translate to higher energy storage in battery?

Point distance program written without a framework

"Eavesdropping" vs "Listen in on"

Why don't programming languages automatically manage the synchronous/asynchronous problem?

Can this note be analyzed as a non-chord tone?

What would be the main consequences for a country leaving the WTO?

Spaces in which all closed sets are regular closed

Reference request: Grassmannian and Plucker coordinates in type B, C, D

Physiological effects of huge anime eyes

Can I calculate next year's exemptions based on this year's refund/amount owed?

Is there a difference between "Fahrstuhl" and "Aufzug"?

Traveling with my 5 year old daughter (as the father) without the mother from Germany to Mexico

Yu-Gi-Oh cards in Python 3

Is there a reasonable and studied concept of reduction between regular languages?

Which one is the true statement?

Can you teleport closer to a creature you are Frightened of?

Is it ok to trim down a tube patch?

How did Beeri the Hittite come up with naming his daughter Yehudit?

Join two tables and create a new one in SQL

1

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

share|improve this question

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♦ 6 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 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
  

  
  
  
  

add a comment | 

1

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

share|improve this question

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♦ 6 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 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
  

  
  
  
  

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

share|improve this question

edited Sep 28 '16 at 9:33

ypercubeᵀᴹ

77.8k11136219

asked Sep 28 '16 at 9:27

user106875user106875

61

share|improve this question

edited Sep 28 '16 at 9:33

ypercubeᵀᴹ

77.8k11136219

asked Sep 28 '16 at 9:27

user106875user106875

61

share|improve this question

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

asked Sep 28 '16 at 9:27

user106875user106875

61

asked Sep 28 '16 at 9:27

user106875user106875

61

1 Answer
1

active

oldest

votes

0

• You need to UNION ALL the two table entries


• Then take the SUM(Category) by GROUP 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.

share|improve this answer

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
});


}
});

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

0

• You need to UNION ALL the two table entries


• Then take the SUM(Category) by GROUP 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.

share|improve this answer

answered Sep 28 '16 at 9:53

ArulkumarArulkumar

858417

add a comment | 

0

• You need to UNION ALL the two table entries


• Then take the SUM(Category) by GROUP 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.

share|improve this answer

answered Sep 28 '16 at 9:53

ArulkumarArulkumar

858417

add a comment | 

• You need to UNION ALL the two table entries


• Then take the SUM(Category) by GROUP 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.

share|improve this answer

answered Sep 28 '16 at 9:53

ArulkumarArulkumar

858417

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

share|improve this answer

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