Selecting Winner based on 2 player data

Question

So i have two tables which based on below :

Table : Player 
Code  Name  Address 
-------------------------
001   John   Miami
002   Winda  Washington
003   Ferry  New York
004   Chase  Texas
________________________

Table : Match
No      Date        Player1     Player2     Result
0001    01/11/17    001         003         003
0002    01/11/17    002         004         002
0003    02/11/17    001         002         001
0004    02/11/17    003         004         004
0005    03/11/17    001         004         001
0006    03/11/17    002         003         002

So based on both tables, I want to create a select query to make result like this :

No      Date        Winner      Loser
0001    01-Nov-17   Ferry       John
0002    01-Nov-17   Winda       Chase 
0003    02-Nov-17   John        Winda
0004    02-Nov-17   Chase       Ferry
0005    03-Nov-17   John        Chase
0006    03-Nov-17   Winda       Ferry

I was told by a friend to use not equal to operator (!=) Previously I Already made this

 
select b.No, b.Date, c.Name as "Winner", d.Name as "Loser"
from match b, player c, player d
where b.Result = c.code >> then i got stuck

Any ideas regarding below problem?

Many thanks!

MT0 MT0 · Accepted Answer · 2017-11-01T16:04:51

Use the ANSI Join syntax and a CASE expression:

select m.No,
       m."Date",
       CASE m.Result WHEN m.Player1 THEN p1.Name ELSE p2.Name END as Winner,
       CASE m.Result WHEN m.Player1 THEN p2.Name ELSE p1.Name END as Loser
FROM   match m
       INNER JOIN player p1
       ON ( m.Player1 = p1.code )
       INNER JOIN player p2
       ON ( m.Player2 = p2.code )

Selecting Winner based on 2 player data

1 Answers