Search code examples
mysqlrdbms

MySQL complex conditional select query

I have a table where I am storing result of teams. A team id can be homeTeam or awayTeam. I want to retrieve the last 5 matches that a team has played.

Please see my SQL fiddle http://sqlfiddle.com/#!9/701305/1

enter image description here

If you see the result, here I am trying to get the last 5 matches stats of team id 165

In the first row, 165 is a homeTeam and you can see homeTeamPoint(3)>awayTeamPoint(0) so in this case I want to return W similarly if the team lose then it should L and if same points then D

So out put I am expecting can be in two ways

Out put : W,L,W,W,L or Out put can be multiple rows..

Please help me it's too complex for me.

Thank you so much.

Solution

  • You can use multiple CASE .. WHEN statement(s) to get result from the perspective of team_id = 65.

    SELECT 
  homeTeam, 
  awayTeam, 
  homeTeamPoint, 
  awayTeamPoint, 
  CASE 
    WHEN homeTeamPoint = awayTeamPoint 
      THEN 'D'
    WHEN `homeTeam` = 165 AND homeTeamPoint > awayTeamPoint 
      THEN 'W'
    WHEN `homeTeam` = 165 AND homeTeamPoint < awayTeamPoint 
      THEN 'L'
    WHEN `awayTeam` = 165 AND homeTeamPoint < awayTeamPoint 
      THEN 'W'
    WHEN `awayTeam` = 165 AND homeTeamPoint > awayTeamPoint 
      THEN 'L'
  END AS result   
FROM fixtureandresults 
WHERE (`homeTeam` = 165 OR awayTeam=165) 
  AND over = 1 
ORDER BY id DESC LIMIT 5

    DB Fiddle DEMO