Hello,

I've put together a website The Ultimate Showdown

The website features two tables.

fighters
id (primary key)
name

fights
winner (foreign key to fighters.id)
loser (foreign key to fighters.id)

Each fighter can be in many many fights and will either be a winner or a loser. Each fight must have exactly one winner and one loser.

I'm trying to build a view for fighter's statistics
fighterStats
fighterId (fighters.id)
wins (number of wins a fighter has obtained)
losses (number of losses a fighter has obtained)
totalFights (wins+losses)
powerLevel ((wins+10)/(totalFights+20)*10000) (number ranging from 0-10000)

I can build this view using sub queries in my select i.e.

select
ftr.id as fighterId,
IFNULL((select count(1) from fights where winner = ftr.id group by winner), 0) as wins,
IFNULL((select count(1) from fights where loser = ftr.id group by loser), 0) as losses,
IFNULL(((select count(1) from fights where winner = ftr.id group by winner)+(select count(1) from fights where loser = ftr.id group by loser)), 0) as totalFights,
ROUND(((IFNULL((select count(1) from fights where winner = ftr.id group by winner),0) + 10)/((IFNULL((select count(1) from fights where winner = ftr.id group by winner),0)+IFNULL((select count(1) from fights where loser = ftr.id group by loser),0))+ 20))* 10000) as powerLevel
from fighters ftr

While this works there is a large amount of calculation and redundant calculation at that because you can't use wins+losses for totalFights and instead you have to resort to a reproducing both the wins and losses query portion in their entirety summed together. When you work out powerLevel it gets so complicated that it's hard to follow. This worked... but it wasn't very efficient and as the fights grow I imagine it will become even less so.

I tried doing something like

select ftr.id, count(w.winner) wins, count(l.loser) losses
from fighters ftr
left outer join fights w
on w.winner = ftr.id
left outer join fights l
on l.loser = ftr.id
group by ftr.id

but this results in wrong data as the wins and losses end up at the same total.

I then tried something with a sub query in the from:

select ftr.id fighterId, wins, losses, wins+losses as totalFights, ROUND((wins+10)/(wins+losses+20)*10000) as powerLevel
from fighters ftr
join (
select count(1) wins, winner from fights
group by winner
) as wins
on ftr.id = wins.winner
join (
select count(1) losses, loser from fights
group by loser
) as losses
on ftr.id = losses.loser

This worked fantastically but unfortunately views can't use subqueries in the from clause.

Does anyone have any suggestions on a better way to do this? Maybe a different query I haven't though of. Or possibly a different table structure?

Any advice is greatly appreciated.

views can't use subqueries in the from clause? really?

i mean, i haven't run across that myself before since i don't need a lot of views, but really?

oh well, you prolly can't use this then...