sql - INNER JOIN one column on more than one column in another able? -

i have 3 tables following:

ids:

id | id_code 1  | abcde 2  | jklmn 3  | pqrst 

players:

full_name| initials john s.  | js anne p.  | ap jen l.   | jl 

games:

id     | player | points | player2 | points2 1      | js     | 2      | ap      | 1  2      | ap     | 1      | jl      | 3  2      | jl     | 3      | js      | 4  3      | jl     | 4      | ap      | 1  

======

i want following output:

id | id_code | full_name | points | full_name_player_2 | points2 

i can't figure out how more 1 join statement...

update

with new structure:

select i.id, i.id_code, p1.full_name, g.points, p2.full_name, g.points2      @ids     inner join @games g on g.id = i.id     inner join @players p1 on g.player = p1.initials     inner join @players p2 on g.player2 = p2.initials 

old database

i think result. let me know if works.

select i.id, i.id_code, p1.full_name, g.points, p2.full_name, g.points2      @ids     inner join @players p1 on p1.id = i.id     inner join @games g on g.player = p1.initials     inner join @players p2 on g.player2 = p2.initials 

test case

declare @ids table (     id int,     id_code nvarchar(200) )  declare @players table (     id int,     full_name nvarchar(200),     initials nvarchar(200) )  declare @games table (     player nvarchar(200),     points int,     player2 nvarchar(200),     points2 int )  insert @ids (id, id_code) values (1, 'abcde') insert @ids (id, id_code) values (2, 'jklmn') insert @ids (id, id_code) values (3, 'pqrst')   insert @players (id, full_name, initials) values (1, 'john s.', 'js') insert @players (id, full_name, initials) values (2, 'anne p.', 'ap') insert @players (id, full_name, initials) values (3, 'jen l.', 'jl')   insert @games (player, points, player2, points2) values ('js', 2, 'ap', 1) insert @games (player, points, player2, points2) values ('ap', 1, 'jl', 3) insert @games (player, points, player2, points2) values ('jl', 3, 'js', 4) insert @games (player, points, player2, points2) values ('jl', 4, 'ap', 1)