CODEWITHSUNDEEP
mysqlsqljoin
user962449
user962449

Reputation: 3873

Need guidance for complicated query

So here's a little background on the system. User's battle and the winner is the one who wins the most rounds. I need help with possibly joining three tables. I have table user one, which stores the user info. Table match which stores match information. Table rounds which stores the winner of every rounds per match, so if a match has 5 rounds, then table rounds will have five rows for that match and will record the winner of each round.

Here's some sample data: Table user: (userid is the primary key)

userid   username
-----------------
  1       Kevin
  2       Sam
  3       Steve
  4       Matt

Table match: (id is the primary key. challenger and challenged are both foreign keys to user.userid)

 id  challenger  challenged  rounds
-----------------------------------
 1      2           3          3
 2      1           2          1
 3      2           3          3
 4      2           4          1

Table rounds: (all fields are the primary key. id is foreign key to match.id and winner is foreign key to user.userid)

 id  round  winner
------------------
 1    1       2
 1    2       2
 1    3       3
 2    1       1
 3    1       2
 3    2       3
 3    3       2
 4    1       4

I'm trying to build a query that will output the following results:

winner  won   loser  won  
------------------------
 Sam     2    Steve   1
 Kevin   1    Sam     0
 Sam     2    Steve   1
 Matt    1    Sam     0

The above results shows the winner and loser of each of match. The won field shows the number of rounds won for that match for the winner and loser respectively.

Does anyone know how I can build the above query?

Upvotes: 1

Views: 111

Answers (3)

Devart
Devart

Reputation: 122042

Try this one -

SELECT
  IF(won1 >= won2, username1, username2) winner,
  IF(won1 >= won2, won1, won2) won,
  IF(won1 < won2, username1, username2) loser,
  IF(won1 < won2, won1, won2) won
FROM (
  SELECT u1.username username1, u2.username username2, COALESCE(r1.rounds, 0) won1, COALESCE(r2.rounds, 0) won2 FROM `match` m
    JOIN user u1
      ON u1.userid = m.challenger
    JOIN user u2
      ON u2.userid = m.challenged
    LEFT JOIN (SELECT id, COUNT(round) rounds, winner FROM rounds GROUP BY id, winner) r1
      ON r1.id = m.id AND r1.winner = u1.userid
    LEFT JOIN (SELECT id, COUNT(round) rounds, winner FROM rounds GROUP BY id, winner) r2
      ON r2.id = m.id AND r2.winner = u2.userid
  ) t

Upvotes: 0

user359040
user359040

Reputation:

Try:

select match_id,
       case when challenger_wins > challenged_wins 
            then challenger_name else challenged_name end) winner,
       case when challenger_wins > challenged_wins 
            then challenger_wins else challenged_wins end) winner_won,
       case when challenger_wins < challenged_wins 
            then challenger_name else challenged_name end) loser,
       case when challenger_wins < challenged_wins 
            then challenger_wins else challenged_wins end) loser_won
from
(select m.id match_id,
        max(case when u.userid = m.challenger 
                 then u.username end) challenger_name,
        sum(case when u.userid = m.challenger 
                 then r.wins else 0 end) challenger_wins,
        max(case when u.userid = m.challenged 
                 then u.username end) challenged_name,
        sum(case when u.userid = m.challenged 
                 then r.wins else 0 end) challenged_wins
 from match m
 join user u on u.userid in (m.challenger, m.challenged)
 left join 
 (select id, winner, count(*) wins
  from round
  group by id, winner) r
 on m.id = r.id and u.userid = r.winner
 group by m.id) v

Upvotes: 0

Michał Powaga
Michał Powaga

Reputation: 23183

select 
    case when w1 > w2 then u1 else u2 end as winner,
    case when w1 > w2 then w1 else w2 end as won,
    case when w1 > w2 then u2 else u1 end as loser,
    case when w1 > w2 then w2 else w1 end as won
from (
    select m.id, u1.username as u1, u2.username as u2, 
        count(r1.winner) as w1,
        count(r2.winner) as w2
    from match m
    join user u1 on m.challenger = u1.userid
    join user u2 on m.challenged = u2.userid
    join rounds r on r.id = m.id
    left join rounds r1 on r.id = r1.id and r.round = r1.round and r1.winner = u1.userid
    left join rounds r2 on r.id = r2.id and r.round = r2.round and r2.winner = u2.userid
    group by m.id, u1.username, u2.username
) t

It relies on (Problems with NULL Values):

Aggregate (summary) functions such as COUNT(), MIN(), and SUM() ignore NULL values.

and here it is an advantage.

Upvotes: 1

Related Questions