TABLE player
idPlayer | name | 1 | name1 | 2 | name2 | 3 | name3 | 4 | name4 |
Table matches
idMatch | idPlayer1 | idPlayer2 | date | 1 | 1 | 2 | 03-03 | 2 | 3 | 4 | 03-03 | 3 | 1 | 4 | 03-03 |
Expeted
idMatch | player1 | player2 | date | 1 | name1 | name2 | 03-03 | 2 | name3 | name4 | 03-03 | 3 | name1 | name4 | 03-03 |
I have tried many codes and still have not found a solution.
Advertisement
Answer
I made this
SELECT idMatch, (SELECT name FROM t1 WHERE t2.idPlayer1=t1.idPlayer) as name1, (SELECT name FROM t1 WHERE t2.idPalyer2=t1.idplayer) as name2, date FROM calendario
and it works.