MySQL JOIN values from another table based on (duplicate) values from original table within that table





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







0















I encountered the following problem while trying to work with data from a network visualization.



I have two Tables, one containing the nodes in that network (called Nodes), and the other one the edges connecting them (called edges).



This is the edges table:



| Source| Target | ModularityClass |
|-------|--------|-----------------|
| UserA | UserB | NULL |
| UserA | UserC | NULL |
| UserC | UserD | NULL |
| UserC | UserB | NULL |
| UserE | UserB | NULL |


And this is the nodes table:



| Username| ModularityClass |
|---------|-----------------|
| UserA | 38 |
| UserB | 11 |
| UserC | 38 |
| UserD | 23 |
| UserE | 11 |


The nodes table has a modularityClass value that is assigned to at least 2 rows (Users).



What I want to do now is assigning the modularityClass value from the nodes table for rows having the same value (Users with the same modularityClass i.e. duplicates) to the corresponding row in the edges table which contains these both values (Users). The final table in this example should then look like this:



| Source| Target | ModularityClass |
|-------|--------|-----------------|
| UserA | UserB | NULL |
| UserA | UserC | 38 |
| UserC | UserD | NULL |
| UserC | UserB | NULL |
| UserE | UserB | 11 |


This would obviously lead to certain NULL values in the final table, but this is intended and not a problem.



So basically, I need to check another table for duplicate values (at least two) in a specific column, then get the values of these rows and compare them to two rows of the original table, and JOIN those onto the modularityClassrow.



However, how would you do that?










share|improve this question























  • Just to clarify your goal, do you need a query, or an update on the original edges table?

    – Shidersz
    Jan 3 at 21:22











  • @Shidersz Generally, I'd like to update the original edges table, however if the query works, I can just join the edges table afterwards.

    – mxfilerelatedcache
    Jan 3 at 21:38


















0















I encountered the following problem while trying to work with data from a network visualization.



I have two Tables, one containing the nodes in that network (called Nodes), and the other one the edges connecting them (called edges).



This is the edges table:



| Source| Target | ModularityClass |
|-------|--------|-----------------|
| UserA | UserB | NULL |
| UserA | UserC | NULL |
| UserC | UserD | NULL |
| UserC | UserB | NULL |
| UserE | UserB | NULL |


And this is the nodes table:



| Username| ModularityClass |
|---------|-----------------|
| UserA | 38 |
| UserB | 11 |
| UserC | 38 |
| UserD | 23 |
| UserE | 11 |


The nodes table has a modularityClass value that is assigned to at least 2 rows (Users).



What I want to do now is assigning the modularityClass value from the nodes table for rows having the same value (Users with the same modularityClass i.e. duplicates) to the corresponding row in the edges table which contains these both values (Users). The final table in this example should then look like this:



| Source| Target | ModularityClass |
|-------|--------|-----------------|
| UserA | UserB | NULL |
| UserA | UserC | 38 |
| UserC | UserD | NULL |
| UserC | UserB | NULL |
| UserE | UserB | 11 |


This would obviously lead to certain NULL values in the final table, but this is intended and not a problem.



So basically, I need to check another table for duplicate values (at least two) in a specific column, then get the values of these rows and compare them to two rows of the original table, and JOIN those onto the modularityClassrow.



However, how would you do that?










share|improve this question























  • Just to clarify your goal, do you need a query, or an update on the original edges table?

    – Shidersz
    Jan 3 at 21:22











  • @Shidersz Generally, I'd like to update the original edges table, however if the query works, I can just join the edges table afterwards.

    – mxfilerelatedcache
    Jan 3 at 21:38














0












0








0








I encountered the following problem while trying to work with data from a network visualization.



I have two Tables, one containing the nodes in that network (called Nodes), and the other one the edges connecting them (called edges).



This is the edges table:



| Source| Target | ModularityClass |
|-------|--------|-----------------|
| UserA | UserB | NULL |
| UserA | UserC | NULL |
| UserC | UserD | NULL |
| UserC | UserB | NULL |
| UserE | UserB | NULL |


And this is the nodes table:



| Username| ModularityClass |
|---------|-----------------|
| UserA | 38 |
| UserB | 11 |
| UserC | 38 |
| UserD | 23 |
| UserE | 11 |


The nodes table has a modularityClass value that is assigned to at least 2 rows (Users).



What I want to do now is assigning the modularityClass value from the nodes table for rows having the same value (Users with the same modularityClass i.e. duplicates) to the corresponding row in the edges table which contains these both values (Users). The final table in this example should then look like this:



| Source| Target | ModularityClass |
|-------|--------|-----------------|
| UserA | UserB | NULL |
| UserA | UserC | 38 |
| UserC | UserD | NULL |
| UserC | UserB | NULL |
| UserE | UserB | 11 |


This would obviously lead to certain NULL values in the final table, but this is intended and not a problem.



So basically, I need to check another table for duplicate values (at least two) in a specific column, then get the values of these rows and compare them to two rows of the original table, and JOIN those onto the modularityClassrow.



However, how would you do that?










share|improve this question














I encountered the following problem while trying to work with data from a network visualization.



I have two Tables, one containing the nodes in that network (called Nodes), and the other one the edges connecting them (called edges).



This is the edges table:



| Source| Target | ModularityClass |
|-------|--------|-----------------|
| UserA | UserB | NULL |
| UserA | UserC | NULL |
| UserC | UserD | NULL |
| UserC | UserB | NULL |
| UserE | UserB | NULL |


And this is the nodes table:



| Username| ModularityClass |
|---------|-----------------|
| UserA | 38 |
| UserB | 11 |
| UserC | 38 |
| UserD | 23 |
| UserE | 11 |


The nodes table has a modularityClass value that is assigned to at least 2 rows (Users).



What I want to do now is assigning the modularityClass value from the nodes table for rows having the same value (Users with the same modularityClass i.e. duplicates) to the corresponding row in the edges table which contains these both values (Users). The final table in this example should then look like this:



| Source| Target | ModularityClass |
|-------|--------|-----------------|
| UserA | UserB | NULL |
| UserA | UserC | 38 |
| UserC | UserD | NULL |
| UserC | UserB | NULL |
| UserE | UserB | 11 |


This would obviously lead to certain NULL values in the final table, but this is intended and not a problem.



So basically, I need to check another table for duplicate values (at least two) in a specific column, then get the values of these rows and compare them to two rows of the original table, and JOIN those onto the modularityClassrow.



However, how would you do that?







mysql database row multiple-columns






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jan 3 at 21:09









mxfilerelatedcachemxfilerelatedcache

52




52













  • Just to clarify your goal, do you need a query, or an update on the original edges table?

    – Shidersz
    Jan 3 at 21:22











  • @Shidersz Generally, I'd like to update the original edges table, however if the query works, I can just join the edges table afterwards.

    – mxfilerelatedcache
    Jan 3 at 21:38



















  • Just to clarify your goal, do you need a query, or an update on the original edges table?

    – Shidersz
    Jan 3 at 21:22











  • @Shidersz Generally, I'd like to update the original edges table, however if the query works, I can just join the edges table afterwards.

    – mxfilerelatedcache
    Jan 3 at 21:38

















Just to clarify your goal, do you need a query, or an update on the original edges table?

– Shidersz
Jan 3 at 21:22





Just to clarify your goal, do you need a query, or an update on the original edges table?

– Shidersz
Jan 3 at 21:22













@Shidersz Generally, I'd like to update the original edges table, however if the query works, I can just join the edges table afterwards.

– mxfilerelatedcache
Jan 3 at 21:38





@Shidersz Generally, I'd like to update the original edges table, however if the query works, I can just join the edges table afterwards.

– mxfilerelatedcache
Jan 3 at 21:38












2 Answers
2






active

oldest

votes


















0














You need to join with Nodes twice, once for Source and once for Target. Make the them LEFT JOIN so you get NULL when you don't have all the matches.



SELECT e.Source, e.Target, n2.ModularityClass
FROM edges AS e
LEFT JOIN nodes AS n1 ON n1.username = e.Source
LEFT JOIN nodes AS n2 ON n2.username = e.Target AND n1.ModularityClass = n2.ModularityClass


DEMO






share|improve this answer































    0














    You can left join the nodes for each user and if the modularity class matches.



    UPDATE edges e
    LEFT JOIN nodes ns
    ON ns.username = e.source
    LEFT JOIN nodes nt
    ON nt.username = e.target
    AND nt.modularityclass = ns.modularityclass
    SET e.modularityclass = nt.modularityclass;


    But I recommend not to physically store values, that can be compute from others. That's a recipe for inconsistencies. Each time you change the values in nodes the modularity class in edges might get wrong.



    Better drop the modularityclass from edges and use a view, that shows the edges with the modularity class.



    ALTER TABLE edges
    DROP COLUMN modularityclass;

    CREATE VIEW edges_with_modularityclass
    AS
    SELECT e.source,
    e.target,
    nt.modularityclass
    FROM edges e
    LEFT JOIN nodes ns
    ON ns.username = e.source
    LEFT JOIN nodes nt
    ON nt.username = e.target
    AND nt.modularityclass = ns.modularityclass;


    db<>fiddle






    share|improve this answer
























    • thank you for your answer and the remarks - as I just need it for specific one-time analyses I thought that might be sufficient, but I'll keep that in mind for the future!

      – mxfilerelatedcache
      Jan 3 at 21:50














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


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54029843%2fmysql-join-values-from-another-table-based-on-duplicate-values-from-original-t%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









    0














    You need to join with Nodes twice, once for Source and once for Target. Make the them LEFT JOIN so you get NULL when you don't have all the matches.



    SELECT e.Source, e.Target, n2.ModularityClass
    FROM edges AS e
    LEFT JOIN nodes AS n1 ON n1.username = e.Source
    LEFT JOIN nodes AS n2 ON n2.username = e.Target AND n1.ModularityClass = n2.ModularityClass


    DEMO






    share|improve this answer




























      0














      You need to join with Nodes twice, once for Source and once for Target. Make the them LEFT JOIN so you get NULL when you don't have all the matches.



      SELECT e.Source, e.Target, n2.ModularityClass
      FROM edges AS e
      LEFT JOIN nodes AS n1 ON n1.username = e.Source
      LEFT JOIN nodes AS n2 ON n2.username = e.Target AND n1.ModularityClass = n2.ModularityClass


      DEMO






      share|improve this answer


























        0












        0








        0







        You need to join with Nodes twice, once for Source and once for Target. Make the them LEFT JOIN so you get NULL when you don't have all the matches.



        SELECT e.Source, e.Target, n2.ModularityClass
        FROM edges AS e
        LEFT JOIN nodes AS n1 ON n1.username = e.Source
        LEFT JOIN nodes AS n2 ON n2.username = e.Target AND n1.ModularityClass = n2.ModularityClass


        DEMO






        share|improve this answer













        You need to join with Nodes twice, once for Source and once for Target. Make the them LEFT JOIN so you get NULL when you don't have all the matches.



        SELECT e.Source, e.Target, n2.ModularityClass
        FROM edges AS e
        LEFT JOIN nodes AS n1 ON n1.username = e.Source
        LEFT JOIN nodes AS n2 ON n2.username = e.Target AND n1.ModularityClass = n2.ModularityClass


        DEMO







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 3 at 21:17









        BarmarBarmar

        435k36260363




        435k36260363

























            0














            You can left join the nodes for each user and if the modularity class matches.



            UPDATE edges e
            LEFT JOIN nodes ns
            ON ns.username = e.source
            LEFT JOIN nodes nt
            ON nt.username = e.target
            AND nt.modularityclass = ns.modularityclass
            SET e.modularityclass = nt.modularityclass;


            But I recommend not to physically store values, that can be compute from others. That's a recipe for inconsistencies. Each time you change the values in nodes the modularity class in edges might get wrong.



            Better drop the modularityclass from edges and use a view, that shows the edges with the modularity class.



            ALTER TABLE edges
            DROP COLUMN modularityclass;

            CREATE VIEW edges_with_modularityclass
            AS
            SELECT e.source,
            e.target,
            nt.modularityclass
            FROM edges e
            LEFT JOIN nodes ns
            ON ns.username = e.source
            LEFT JOIN nodes nt
            ON nt.username = e.target
            AND nt.modularityclass = ns.modularityclass;


            db<>fiddle






            share|improve this answer
























            • thank you for your answer and the remarks - as I just need it for specific one-time analyses I thought that might be sufficient, but I'll keep that in mind for the future!

              – mxfilerelatedcache
              Jan 3 at 21:50


















            0














            You can left join the nodes for each user and if the modularity class matches.



            UPDATE edges e
            LEFT JOIN nodes ns
            ON ns.username = e.source
            LEFT JOIN nodes nt
            ON nt.username = e.target
            AND nt.modularityclass = ns.modularityclass
            SET e.modularityclass = nt.modularityclass;


            But I recommend not to physically store values, that can be compute from others. That's a recipe for inconsistencies. Each time you change the values in nodes the modularity class in edges might get wrong.



            Better drop the modularityclass from edges and use a view, that shows the edges with the modularity class.



            ALTER TABLE edges
            DROP COLUMN modularityclass;

            CREATE VIEW edges_with_modularityclass
            AS
            SELECT e.source,
            e.target,
            nt.modularityclass
            FROM edges e
            LEFT JOIN nodes ns
            ON ns.username = e.source
            LEFT JOIN nodes nt
            ON nt.username = e.target
            AND nt.modularityclass = ns.modularityclass;


            db<>fiddle






            share|improve this answer
























            • thank you for your answer and the remarks - as I just need it for specific one-time analyses I thought that might be sufficient, but I'll keep that in mind for the future!

              – mxfilerelatedcache
              Jan 3 at 21:50
















            0












            0








            0







            You can left join the nodes for each user and if the modularity class matches.



            UPDATE edges e
            LEFT JOIN nodes ns
            ON ns.username = e.source
            LEFT JOIN nodes nt
            ON nt.username = e.target
            AND nt.modularityclass = ns.modularityclass
            SET e.modularityclass = nt.modularityclass;


            But I recommend not to physically store values, that can be compute from others. That's a recipe for inconsistencies. Each time you change the values in nodes the modularity class in edges might get wrong.



            Better drop the modularityclass from edges and use a view, that shows the edges with the modularity class.



            ALTER TABLE edges
            DROP COLUMN modularityclass;

            CREATE VIEW edges_with_modularityclass
            AS
            SELECT e.source,
            e.target,
            nt.modularityclass
            FROM edges e
            LEFT JOIN nodes ns
            ON ns.username = e.source
            LEFT JOIN nodes nt
            ON nt.username = e.target
            AND nt.modularityclass = ns.modularityclass;


            db<>fiddle






            share|improve this answer













            You can left join the nodes for each user and if the modularity class matches.



            UPDATE edges e
            LEFT JOIN nodes ns
            ON ns.username = e.source
            LEFT JOIN nodes nt
            ON nt.username = e.target
            AND nt.modularityclass = ns.modularityclass
            SET e.modularityclass = nt.modularityclass;


            But I recommend not to physically store values, that can be compute from others. That's a recipe for inconsistencies. Each time you change the values in nodes the modularity class in edges might get wrong.



            Better drop the modularityclass from edges and use a view, that shows the edges with the modularity class.



            ALTER TABLE edges
            DROP COLUMN modularityclass;

            CREATE VIEW edges_with_modularityclass
            AS
            SELECT e.source,
            e.target,
            nt.modularityclass
            FROM edges e
            LEFT JOIN nodes ns
            ON ns.username = e.source
            LEFT JOIN nodes nt
            ON nt.username = e.target
            AND nt.modularityclass = ns.modularityclass;


            db<>fiddle







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Jan 3 at 21:33









            sticky bitsticky bit

            15.9k111733




            15.9k111733













            • thank you for your answer and the remarks - as I just need it for specific one-time analyses I thought that might be sufficient, but I'll keep that in mind for the future!

              – mxfilerelatedcache
              Jan 3 at 21:50





















            • thank you for your answer and the remarks - as I just need it for specific one-time analyses I thought that might be sufficient, but I'll keep that in mind for the future!

              – mxfilerelatedcache
              Jan 3 at 21:50



















            thank you for your answer and the remarks - as I just need it for specific one-time analyses I thought that might be sufficient, but I'll keep that in mind for the future!

            – mxfilerelatedcache
            Jan 3 at 21:50







            thank you for your answer and the remarks - as I just need it for specific one-time analyses I thought that might be sufficient, but I'll keep that in mind for the future!

            – mxfilerelatedcache
            Jan 3 at 21:50




















            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54029843%2fmysql-join-values-from-another-table-based-on-duplicate-values-from-original-t%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

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas