sfmagic.org Rewrite: Head-to-Head Working SQL Queries
.I’ve stripped some fields from the h2hresults table, and I’ve created some queries that do what I want but may not be the right approach.
Having thought about it further, I’ve decided that tournament_id should (because I need a key into the tournaments table) but that the two resultid columns should go. With playerid and tournament, I can easily fetch the corresponding resultid if I need it. So the columns are now:
match_id
tournament_id
round
player1
player2
wins
losses
draws
match_id itself may be unnecessary.
Having done that, and entered some dummy data, I needed the queries to get results out. I have the following three queries that appear to do what I want, all of which use the same basic approach.
Get the game record between players 99 and 4:
select
sum(
if (player1=99, wins,0) + if(player2=99, losses, 0)
) as gwins,
sum(
if (player1=99, losses,0) + if(player2=99, wins, 0)
) as glosses,
sum(draws) as gdraws
from
h2hresults
where
(player1=99 and player2=4) or (player1=4 and player2=99);
Get the match record between players 99 and 4:
select
sum(
if (player1=99 and wins > losses, 1,0)
) as mwins,
sum(
if (player2=99 and losses < wins, 1,0)
) as mlosses,
sum(
if (player1=99 and losses = wins, 1,0) + if (player2=99 and losses = wins, 1,0)
) as mdraws
from
h2hresults
where
(player1=99 and player2=4) or (player1=4 and player2=99);
Get the player against whom player 99 has the most victories:
select
sum(
if (player1=99 and wins > losses, 1,0)
) as mwins,
player2
from
h2hresults
where
player1=99
group by
player2
order by mwins desc
limit 1;
The problem is that I Have no idea whether or not this is the right approach to take to get this kind of information—I need to do some reading about SQL and MySQL to figure that out. One thing that makes me suspicious is that SQLAlchemy's documentation appears to have no mention of IF, and little mention of SUM, and I can't figure out how to get IF to work. SUM works fine via func.sum, but the same isn't true for IF. That makes me think that the right approach is using subqueries, but I wasn't able to figure out that approach this evening.