Calculated MySQL result

2020-02-15 mysql sql join calculation

I have a database of MMA fighters and their fights, stored in two tables: fighter and fight.

I want to query the database with a given figher id and get a result in rows like this:

Result | Record | Weight| Opponent| Opponent Record | Opponent Weight | Method | Date | Round | Time

The 4 last columns are pretty straight forward, but the other columns are tricky since I don't know if the given fighter id is fighter1 or fighter2:

Result should be taken from fight.winner, which has the values "fighter1", "fighter2", "TBD", "D" or "NC". The result needs to be calculated based on if the given fighter id is fighter1 or fighter2, so it shows either "W", "L", "TBD", "D" or "NC".

Record and Opponent Record should be taken from fight.fighter1_record_after_result or fight.fighter2_record_after_result, depending on if the given fighter id is fighter1 or fighter2.

Weight and Opponent Weight should be taken from fight.fighter1_weight or fight.fighter2_weight, depending on if the given fighter id is fighter1 or fighter2.

Opponent should be taken from fighter.name. Who should be regarded opponent, depends on wether the given fighter id is fighter1 or fighter2.

Data structure and sample data at DB Fiddle: https://www.db-fiddle.com/f/5LjJqEM6yMYuShZybcsrJ5/0

Answers

union all might come handy here: instead of implementing complicated conditional logic in a single query, we can combine the result sets of two queries, that seach for the targer fighter in either column fighter1 or column fighter2:

select
    case t.winner
        when 'fighter1' then 'W'
        when 'fighter2' then 'L'
        else t.winner
    end result,
    t.fighter1_record_after_result record,
    t.fighter1_weight weight,
    r.name opponent,
    t.fighter2_record_after_result oppononed_record,
    t.fighter2_weight opponent_weight,
    t.method,
    t.date,
    t.round,
    t.time
from fight t
left join fighter r on r.id = t.fighter2
where t.fighter1 = 64
union all
select
    case t.winner
        when 'fighter2' then 'W'
        when 'fighter1' then 'L'
        else t.winner
    end,
    t.fighter2_record_after_result,
    t.fighter2_weight,
    r.name,
    t.fighter1_record_after_result,
    t.fighter1_weight,
    t.method,
    t.date,
    t.round,
    t.time
from fight t
left join fighter r on r.id = t.fighter1
where t.fighter2 = 64

Both parameters should be given the value of the fighter that you are generating the report for.

Related