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.enter image description hereenter image description here



I have attached the samples for your reference










share|improve this question
















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
















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.enter image description hereenter image description here



I have attached the samples for your reference










share|improve this question
















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














1












1








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.enter image description hereenter image description here



I have attached the samples for your reference










share|improve this question
















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.enter image description hereenter image description here



I have attached the samples for your reference







ms-access






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Sep 28 '16 at 9:33









ypercubeᵀᴹ

77.8k11136219




77.8k11136219










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



















  • 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

















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










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
























    Your Answer








    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


















    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









    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




























      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


























        0












        0








        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














        • 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












        share|improve this answer



        share|improve this answer










        answered Sep 28 '16 at 9:53









        ArulkumarArulkumar

        858417




        858417






























            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh