Calculated MySQL result2020-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
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
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.
- How do I import an SQL file using the command line in MySQL?
- How to output MySQL query results in CSV format?
- Retrieving the last record in each group - MySQL
- Should I use the datetime or timestamp data type in MySQL?
- How do I connect to a MySQL Database in Python?
- Insert results of a stored procedure into a temporary table
- Insert into a MySQL table or update if exists
- How to reset AUTO_INCREMENT in MySQL?
- How to do a FULL OUTER JOIN in MySQL?
- Can I concatenate multiple MySQL rows into one field?