hadi hadi - 3 years ago 166
SQL Question

inner join (multi foreign key of a single table)

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 Source

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 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download