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;
}
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 modularityClass
row.
However, how would you do that?
mysql database row multiple-columns
add a comment |
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 modularityClass
row.
However, how would you do that?
mysql database row multiple-columns
Just to clarify your goal, do you need a query, or an update on the originaledges
table?
– Shidersz
Jan 3 at 21:22
@Shidersz Generally, I'd like to update the originaledges
table, however if the query works, I can just join theedges
table afterwards.
– mxfilerelatedcache
Jan 3 at 21:38
add a comment |
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 modularityClass
row.
However, how would you do that?
mysql database row multiple-columns
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 modularityClass
row.
However, how would you do that?
mysql database row multiple-columns
mysql database row multiple-columns
asked Jan 3 at 21:09
mxfilerelatedcachemxfilerelatedcache
52
52
Just to clarify your goal, do you need a query, or an update on the originaledges
table?
– Shidersz
Jan 3 at 21:22
@Shidersz Generally, I'd like to update the originaledges
table, however if the query works, I can just join theedges
table afterwards.
– mxfilerelatedcache
Jan 3 at 21:38
add a comment |
Just to clarify your goal, do you need a query, or an update on the originaledges
table?
– Shidersz
Jan 3 at 21:22
@Shidersz Generally, I'd like to update the originaledges
table, however if the query works, I can just join theedges
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
add a comment |
2 Answers
2
active
oldest
votes
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
add a comment |
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
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
add a comment |
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
});
}
});
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%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
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
add a comment |
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
add a comment |
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 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
answered Jan 3 at 21:17
BarmarBarmar
435k36260363
435k36260363
add a comment |
add a comment |
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
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
add a comment |
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
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
add a comment |
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
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
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
add a comment |
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
add a comment |
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.
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%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
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
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 theedges
table afterwards.– mxfilerelatedcache
Jan 3 at 21:38