CREATE TABLE Match
(
player1 integer,
player2 integer,
score varchar(10)
)
CREATE TABLE Players
(
ID integer,
name varchar(10)
)
insert into Match
values
( 3, 2, '3:1'),
( 2, 1, '3:1'),
( 3, 1, '3:1'),
( 1, 2, '3:1'),
( 1, 3, '3:1'),
( 2, 3, '3:1')
insert into Players
values (1, 'A'),
(2, 'B'),
(3, 'C')
declare @P1 integer
declare @P2 integer
set @P1 = 1;
set @P2 = 3;
select P1.name, P2.name, M.score
from Match M
join Players P1 on P1.id = M.player1
join Players p2 on P2.id = M.player2
where (P1.id = @P1 and P2.id = @P2) or (P1.id = @P2 and P2.id = @P1)