1. Registered User
Join Date
Dec 2007
Posts
23

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?

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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...
Code:
```SELECT fighterId
, wins
, losses
, wins+losses AS totalFights
, (wins+10)/(wins+losses+20)*10000 AS powerLevel
FROM ( SELECT t.id AS fighterId
, ( SELECT COUNT(*)
FROM fights
WHERE winner = t.id
GROUP
BY winner) AS wins
, ( SELECT COUNT(*)
FROM fights
WHERE loser = t.id
GROUP
BY loser) AS losses
FROM fighters AS t
) AS d```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•