MySQL: Join based on multiple columns

Question

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

disatisfieddinosaur · Accepted Answer · 2013-08-11 22:27:06Z

1

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, 2013 at 22:27

disatisfieddinosaur

1,55010 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wirus Over a year ago

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 Over a year ago

+1 from my side for providing @Jim with the query, with small bug, but still... the most important part is ok.

Wirus · Accepted Answer · 2013-08-12 05:59:46Z

0

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?

edited Aug 12, 2013 at 5:59

answered Aug 11, 2013 at 22:47

Wirus

1,19010 silver badges10 bronze badges

1 Comment

Jim Over a year ago

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

Collectives™ on Stack Overflow

MySQL: Join based on multiple columns

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related