Bdtjtk

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?

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

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