inner join (multi foreign key of a single table)

Question

i need your help

i have These tables in mssql server database :

match
---------------
matchid           int;
palyerA_fk        int;
playerB_fk        int;
playerA_scores    int;
playerB_scores    int;



players
---------------
id               int;
fullname         nvarchar;

and i need this view :

Resultsview
----------------
PlayerA_fullname  
PalyerA_scores 
PlayerB_fullname 
PlayerB_scores

please help me to create that view, thanks

. .

this query is not returning any rows:

select a.fullname as playerAfullname , b.fullname as playerBfull name ,
 match.playerA_scores , match.playerB_scores 
from match 
inner join players as a on match.palyerA_fk = a.id 
inner join players as b on match.palyerB_fk = b.id

Answer 1

Check this

select a.fullname as playerAfullname , b.fullname as playerBfullname ,
m.playerA_scores , m.playerB_scores 
from match as m
inner join players as a on m.palyerA_fk = a.id 
inner join players as b on m.palyerB_fk = b.id