CODEWITHSUNDEEP
mysql
Jim
Jim

Reputation: 1357

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

Upvotes: 0

Views: 74

Answers (2)

Wirus
Wirus

Reputation: 1190

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?

Upvotes: 0

disatisfieddinosaur
disatisfieddinosaur

Reputation: 1550

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

Upvotes: 1

Related Questions