MySQL: Join based on multiple columns
I need to join table_1 and table_2 in MySQL and compare which user has the most winnings. Then update table_2.winner with the user id which has won..
table 1
city user winnings
1 a 99
1 b 0
1 c 50
1 d 2
table 2
city user_1 user_2 winner
1 a b a
1 c d 50
However I'm struggling to figure out how to join the tables thus far I have
SELECT table_1.winnings AS win_a, table_1.winnings AS win_b
FROM table_1, table_2
WHERE table_2.user_1 = table_1.user
AND table_2.user_2 = table_1.user
http://sqlfiddle.com/#!2/c855b/1
mysql
edited Nov 16 '18 at 5:55
Cœur
19.1k9114155
asked Aug 11 '13 at 22:20
JimJim
59221228
add a comment |
I need to join table_1 and table_2 in MySQL and compare which user has the most winnings. Then update table_2.winner with the user id which has won..
table 1
city user winnings
1 a 99
1 b 0
1 c 50
1 d 2
table 2
city user_1 user_2 winner
1 a b a
1 c d 50
However I'm struggling to figure out how to join the tables thus far I have
SELECT table_1.winnings AS win_a, table_1.winnings AS win_b
FROM table_1, table_2
WHERE table_2.user_1 = table_1.user
AND table_2.user_2 = table_1.user
http://sqlfiddle.com/#!2/c855b/1
mysql
edited Nov 16 '18 at 5:55
Cœur
19.1k9114155
asked Aug 11 '13 at 22:20
JimJim
59221228
Any update on this?
– Wirus
Aug 12 '13 at 19:35
add a comment |
I need to join table_1 and table_2 in MySQL and compare which user has the most winnings. Then update table_2.winner with the user id which has won..
table 1
city user winnings
1 a 99
1 b 0
1 c 50
1 d 2
table 2
city user_1 user_2 winner
1 a b a
1 c d 50
However I'm struggling to figure out how to join the tables thus far I have
SELECT table_1.winnings AS win_a, table_1.winnings AS win_b
FROM table_1, table_2
WHERE table_2.user_1 = table_1.user
AND table_2.user_2 = table_1.user
http://sqlfiddle.com/#!2/c855b/1
mysql
edited Nov 16 '18 at 5:55
Cœur
19.1k9114155
asked Aug 11 '13 at 22:20
JimJim
59221228
I need to join table_1 and table_2 in MySQL and compare which user has the most winnings. Then update table_2.winner with the user id which has won..
table 1
city user winnings
1 a 99
1 b 0
1 c 50
1 d 2
table 2
city user_1 user_2 winner
1 a b a
1 c d 50
However I'm struggling to figure out how to join the tables thus far I have
SELECT table_1.winnings AS win_a, table_1.winnings AS win_b
FROM table_1, table_2
WHERE table_2.user_1 = table_1.user
AND table_2.user_2 = table_1.user
http://sqlfiddle.com/#!2/c855b/1
mysql
mysql
edited Nov 16 '18 at 5:55
Cœur
19.1k9114155
asked Aug 11 '13 at 22:20
JimJim
59221228
edited Nov 16 '18 at 5:55
Cœur
19.1k9114155
asked Aug 11 '13 at 22:20
JimJim
59221228
edited Nov 16 '18 at 5:55
Cœur
19.1k9114155
edited Nov 16 '18 at 5:55
Cœur
19.1k9114155
edited Nov 16 '18 at 5:55
Cœur
19.1k9114155
19.1k9114155
asked Aug 11 '13 at 22:20
JimJim
59221228
asked Aug 11 '13 at 22:20
JimJim
59221228
asked Aug 11 '13 at 22:20
JimJim
59221228
59221228
Any update on this?
– Wirus
Aug 12 '13 at 19:35
add a comment |
Any update on this?
– Wirus
Aug 12 '13 at 19:35
Any update on this?
– Wirus
Aug 12 '13 at 19:35
Any update on this?
– Wirus
Aug 12 '13 at 19:35
add a comment |
2 Answers
2
active
oldest
votes
I just used @skishore's query, a bit fixed, because it is broken for draws. The one that takes draws under consideration would be
SELECT
case when user1.winnings > user2.winnings then user1.user
when user2.winnings > user1.winnings then user2.user
else null
end
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
But apart from this, I still don't get the purpose. I wrote this in comment to @skishore answer, but paste it here also. Consider the case:
User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C
The second question is - why do you need this stored in a separate table? Winnings number will be dynamically changing so you would have to create trigger to keep winner column on table2 up to date. Can't you just get winner using this query?
answered Aug 11 '13 at 22:47
WirusWirus
945810
Hi Wirus, thank for your answer, I actually intend to store the result in table 1, however I didn't want to put my exact question down as then I would get an exact answer. I wanted something to get me started so I could write the code myself ;)
– Jim
Aug 16 '13 at 14:03
add a comment |
You can join against the table multiple times like this:
SELECT IF(user1.winnings > user2.winnings, "user1", "user2")
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
http://sqlfiddle.com/#!2/c855b/16
answered Aug 11 '13 at 22:27
disatisfieddinosaurdisatisfieddinosaur
1,136512
I know that you made this query basing on provided data model and it's ok assuming we got OP's intention right, but what's the purpose of this query? User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C...
– Wirus
Aug 11 '13 at 22:35
+1 from my side for providing @Jim with the query, with small bug, but still... the most important part is ok.
– Wirus
Aug 11 '13 at 22: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%2f18177277%2fmysql-join-based-on-multiple-columns%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
I just used @skishore's query, a bit fixed, because it is broken for draws. The one that takes draws under consideration would be
SELECT
case when user1.winnings > user2.winnings then user1.user
when user2.winnings > user1.winnings then user2.user
else null
end
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
But apart from this, I still don't get the purpose. I wrote this in comment to @skishore answer, but paste it here also. Consider the case:
User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C
The second question is - why do you need this stored in a separate table? Winnings number will be dynamically changing so you would have to create trigger to keep winner column on table2 up to date. Can't you just get winner using this query?
answered Aug 11 '13 at 22:47
WirusWirus
945810
Hi Wirus, thank for your answer, I actually intend to store the result in table 1, however I didn't want to put my exact question down as then I would get an exact answer. I wanted something to get me started so I could write the code myself ;)
– Jim
Aug 16 '13 at 14:03
add a comment |
I just used @skishore's query, a bit fixed, because it is broken for draws. The one that takes draws under consideration would be
SELECT
case when user1.winnings > user2.winnings then user1.user
when user2.winnings > user1.winnings then user2.user
else null
end
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
But apart from this, I still don't get the purpose. I wrote this in comment to @skishore answer, but paste it here also. Consider the case:
User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C
The second question is - why do you need this stored in a separate table? Winnings number will be dynamically changing so you would have to create trigger to keep winner column on table2 up to date. Can't you just get winner using this query?
answered Aug 11 '13 at 22:47
WirusWirus
945810
Hi Wirus, thank for your answer, I actually intend to store the result in table 1, however I didn't want to put my exact question down as then I would get an exact answer. I wanted something to get me started so I could write the code myself ;)
– Jim
Aug 16 '13 at 14:03
add a comment |
I just used @skishore's query, a bit fixed, because it is broken for draws. The one that takes draws under consideration would be
SELECT
case when user1.winnings > user2.winnings then user1.user
when user2.winnings > user1.winnings then user2.user
else null
end
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
But apart from this, I still don't get the purpose. I wrote this in comment to @skishore answer, but paste it here also. Consider the case:
User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C
The second question is - why do you need this stored in a separate table? Winnings number will be dynamically changing so you would have to create trigger to keep winner column on table2 up to date. Can't you just get winner using this query?
answered Aug 11 '13 at 22:47
WirusWirus
945810
I just used @skishore's query, a bit fixed, because it is broken for draws. The one that takes draws under consideration would be
SELECT
case when user1.winnings > user2.winnings then user1.user
when user2.winnings > user1.winnings then user2.user
else null
end
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
But apart from this, I still don't get the purpose. I wrote this in comment to @skishore answer, but paste it here also. Consider the case:
User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C
The second question is - why do you need this stored in a separate table? Winnings number will be dynamically changing so you would have to create trigger to keep winner column on table2 up to date. Can't you just get winner using this query?
answered Aug 11 '13 at 22:47
WirusWirus
945810
edited Aug 12 '13 at 5:59
answered Aug 11 '13 at 22:47
WirusWirus
945810
answered Aug 11 '13 at 22:47
WirusWirus
945810
answered Aug 11 '13 at 22:47
WirusWirus
945810
945810
Hi Wirus, thank for your answer, I actually intend to store the result in table 1, however I didn't want to put my exact question down as then I would get an exact answer. I wanted something to get me started so I could write the code myself ;)
– Jim
Aug 16 '13 at 14:03
add a comment |
Hi Wirus, thank for your answer, I actually intend to store the result in table 1, however I didn't want to put my exact question down as then I would get an exact answer. I wanted something to get me started so I could write the code myself ;)
– Jim
Aug 16 '13 at 14:03
Hi Wirus, thank for your answer, I actually intend to store the result in table 1, however I didn't want to put my exact question down as then I would get an exact answer. I wanted something to get me started so I could write the code myself ;)
– Jim
Aug 16 '13 at 14:03
Hi Wirus, thank for your answer, I actually intend to store the result in table 1, however I didn't want to put my exact question down as then I would get an exact answer. I wanted something to get me started so I could write the code myself ;)
– Jim
Aug 16 '13 at 14:03
add a comment |
You can join against the table multiple times like this:
SELECT IF(user1.winnings > user2.winnings, "user1", "user2")
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
http://sqlfiddle.com/#!2/c855b/16
answered Aug 11 '13 at 22:27
disatisfieddinosaurdisatisfieddinosaur
1,136512
I know that you made this query basing on provided data model and it's ok assuming we got OP's intention right, but what's the purpose of this query? User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C...
– Wirus
Aug 11 '13 at 22:35
+1 from my side for providing @Jim with the query, with small bug, but still... the most important part is ok.
– Wirus
Aug 11 '13 at 22:50
add a comment |
You can join against the table multiple times like this:
SELECT IF(user1.winnings > user2.winnings, "user1", "user2")
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
http://sqlfiddle.com/#!2/c855b/16
answered Aug 11 '13 at 22:27
disatisfieddinosaurdisatisfieddinosaur
1,136512
I know that you made this query basing on provided data model and it's ok assuming we got OP's intention right, but what's the purpose of this query? User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C...
– Wirus
Aug 11 '13 at 22:35
+1 from my side for providing @Jim with the query, with small bug, but still... the most important part is ok.
– Wirus
Aug 11 '13 at 22:50
add a comment |
You can join against the table multiple times like this:
SELECT IF(user1.winnings > user2.winnings, "user1", "user2")
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
http://sqlfiddle.com/#!2/c855b/16
answered Aug 11 '13 at 22:27
disatisfieddinosaurdisatisfieddinosaur
1,136512
You can join against the table multiple times like this:
SELECT IF(user1.winnings > user2.winnings, "user1", "user2")
FROM table_2 games
JOIN table_1 user1 ON games.user_1 = user1.user
JOIN table_1 user2 ON games.user_2 = user2.user
http://sqlfiddle.com/#!2/c855b/16
answered Aug 11 '13 at 22:27
disatisfieddinosaurdisatisfieddinosaur
1,136512
answered Aug 11 '13 at 22:27
disatisfieddinosaurdisatisfieddinosaur
1,136512
answered Aug 11 '13 at 22:27
disatisfieddinosaurdisatisfieddinosaur
1,136512
answered Aug 11 '13 at 22:27
disatisfieddinosaurdisatisfieddinosaur
1,136512
1,136512
I know that you made this query basing on provided data model and it's ok assuming we got OP's intention right, but what's the purpose of this query? User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C...
– Wirus
Aug 11 '13 at 22:35
+1 from my side for providing @Jim with the query, with small bug, but still... the most important part is ok.
– Wirus
Aug 11 '13 at 22:50
add a comment |
I know that you made this query basing on provided data model and it's ok assuming we got OP's intention right, but what's the purpose of this query? User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C...
– Wirus
Aug 11 '13 at 22:35
+1 from my side for providing @Jim with the query, with small bug, but still... the most important part is ok.
– Wirus
Aug 11 '13 at 22:50
I know that you made this query basing on provided data model and it's ok assuming we got OP's intention right, but what's the purpose of this query? User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C...
– Wirus
Aug 11 '13 at 22:35
I know that you made this query basing on provided data model and it's ok assuming we got OP's intention right, but what's the purpose of this query? User c won 99 matches played with user b, user d on the other hand won 2 matches played with user c. But who will be the winner between c and d? C...
– Wirus
Aug 11 '13 at 22:35
+1 from my side for providing @Jim with the query, with small bug, but still... the most important part is ok.
– Wirus
Aug 11 '13 at 22:50
+1 from my side for providing @Jim with the query, with small bug, but still... the most important part is ok.
– Wirus
Aug 11 '13 at 22: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%2f18177277%2fmysql-join-based-on-multiple-columns%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
Any update on this?
– Wirus
Aug 12 '13 at 19:35