clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601472 fiddles created (47960 in the last week).

create table Match( id int, matchdate date, home_player1 int, home_player2 int, home_player3 int, away_player1 int, away_player2 int, away_player3 int)
 hidden batch(es)


create table Player ( id int, name varchar(100) )
 hidden batch(es)


insert into Player(id, name) values (1, 'Bernd Leno (1)'), (2, 'Aaron Ramsdate (32)'), (3, 'Kieran Tierney (3)'), (4, 'Nick Pope (1)'), (5, 'Will Norris (25)'), (6, 'Wayne Hennessey (13)')
 hidden batch(es)


insert into Match (id, matchdate, home_player1, home_player2, home_player3, away_player1, away_player2, away_player3) values (1, '2021-09-29', 1, 2, 3, 4, 5, 6), (2, '2021-09-30', 4, 5, 6, 1, 2, 3)
 hidden batch(es)


create table MatchPlayer (match_id int, player_id int)
 hidden batch(es)


select match_id, player_id from (select Match.id as match_id, Match.home_player1 as player_id from Match UNION ALL select Match.id as match_id, Match.home_player2 as player_id from Match UNION ALL select Match.id as match_id, Match.home_player3 as player_id from Match UNION ALL select Match.id as match_id, Match.away_player1 as player_id from Match UNION ALL select Match.id as match_id, Match.away_player2 as player_id from Match UNION ALL select Match.id as match_id, Match.away_player3 as player_id from Match ) as unpivottable
match_id player_id
1 1
2 4
1 2
2 5
1 3
2 6
1 4
2 1
1 5
2 2
1 6
2 3
 hidden batch(es)


insert into matchplayer select match_id, player_id from (select Match.id as match_id, Match.home_player1 as player_id from Match UNION ALL select Match.id as match_id, Match.home_player2 as player_id from Match UNION ALL select Match.id as match_id, Match.home_player3 as player_id from Match UNION ALL select Match.id as match_id, Match.away_player1 as player_id from Match UNION ALL select Match.id as match_id, Match.away_player2 as player_id from Match UNION ALL select Match.id as match_id, Match.away_player3 as player_id from Match ) as unpivottable where unpivottable.match_id
 hidden batch(es)


SELECT m.id, m.matchdate, p.name FROM Match as m JOIN MatchPlayer as mp ON mp.match_id = m.id JOIN Player as p ON mp.player_id = p.id
id matchdate name
1 2021-09-29 Bernd Leno (1)
1 2021-09-29 Aaron Ramsdate (32)
1 2021-09-29 Kieran Tierney (3)
1 2021-09-29 Nick Pope (1)
1 2021-09-29 Will Norris (25)
1 2021-09-29 Wayne Hennessey (13)
2 2021-09-30 Bernd Leno (1)
2 2021-09-30 Aaron Ramsdate (32)
2 2021-09-30 Kieran Tierney (3)
2 2021-09-30 Nick Pope (1)
2 2021-09-30 Will Norris (25)
2 2021-09-30 Wayne Hennessey (13)
 hidden batch(es)