1

I have two tables named "Player" and "Match". In "Player", There are two columns: Player.id and Player.name and in the "Match" table I have many columns related to about 20000 soccer matches, 22 of them relating to the players that have played in a particular match.

In the "Match" table, I have player ids in their respective columns, lets say I want a Query that shows the names of the Players in each match.(I want to replace their ids with their names). to do so, I have to join these two tables 22 times, one for each player. For example to get player1's name:

select Match.id, Match.date, p1.name
from Match, Player as p1
Where Match.home_player1 == p1.id

Now if I want to get for example player2's name too, I have to do another join. which will be:

select Match.id, Match.date, p1.name, p2.name
from Match, Player as p1, Player as p2
Where Match.home_player1 == p1.id and Match.home_player2 == p2.id

And if I want to get all the 22 players, i have to do 22 joins. This is both inconvenient and in my opinion very inefficient as the number of rows is large.

Is there any convenient way to do what i want?

If it helps, the database is in sqlite and it does not matter if all the names end up in one column with a separator between them.

5
  • 1
    Do you make a table with home_player1, home_player2, ... home_player22 columns? If so then search for UNPIVOT feature in your DBMS... or join 22 table copies. And the best way - normalize the structure.
    – Akina
    Oct 4 '21 at 11:25
  • If you could include which DBMS system you are using, then @Akina or somebody else could provide you with an UNPIVOT solution. I'm not very good at that, hence my suggestion for a DB redesign.
    – John K. N.
    Oct 4 '21 at 11:35
  • 1
    Unfortunately, the data is from a downloaded data set and is not my design.@Akina I will search for that. thank you. @john-k-n i have included my dbms and its sqlite. i'll check your answer. thanks Oct 4 '21 at 11:39
  • Does the Match table contain columns named away_player1 ... away_player11 ? Guessing, because of the home_player1 ... etc.
    – John K. N.
    Oct 4 '21 at 13:24
  • Yes. There is 11 columns for each team's players in this table. Oct 4 '21 at 14:41
2

A better (design) solution would be to have another table named matchplayer. This simplistic table would consist of the two columns match_id and player_id.

You then JOIN on the match_id and then on the player_id.

SELECT m.id, m.date, p.name from 
FROM Match as m 
    JOIN matchplayer as mp
        ON mp.match_id = m.id
    JOIN Player as p
        ON mp.player_id = p.id

It doesn't matter if you have only 11, 10 or 12 players for that match. You always get a full list of players that participated in a given match. This could even include substitutions.

Small Example to Unpivot into New Table

I've created a small example on db<>fiddle to show you how you could unpivot the data from the match table into a new table to retrieve the player's name with slightly more ease.

Player Table

create table Player (
id int,
name varchar(100)
)

...and some data:

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)')

Match Table

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
)

...and some data to go with it:

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)

MatchPlayer Table

This is the table we are going to use to join afterwards

create table MatchPlayer 
(
    match_id int, 
    player_id int
)

Selected Unpivoted Data

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

Results in:

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

Insert Selected Unpivoted Data into MatchPlayer

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 

Retrieve Simple Match Overview

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

Results in:

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)

This is a very quick example bases on some assumptions.

On a Side Note

The data of the players was taken from Premier League's homepage as of today (2021-10-04) from the teams Burnley FC and Arsenal.

4
  • 1
    As i mentioned, this is a downloaded dump, so design is what it is. any suggestions to convert my data's design to what you described? Oct 4 '21 at 11:42
  • Sadly sqlite does not support UNPIVOT so you'll have to do with this: Turn SQLite columns to rows.
    – John K. N.
    Oct 4 '21 at 11:48
  • Yes, it seems i have to do either the join or creating the Intermediary table(that you described) by hand. Oct 4 '21 at 12:19
  • I've added an example on how you could achieve the desired MatchPlayer table. You could split it into home and away columns. You could then have a MatchPlayer table with three columns match_id, player_id, home_flag. Hope this gets you flying.
    – John K. N.
    Oct 4 '21 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.