I’m new to SQL (currently using mysql) and I’m hopeful someone can help with a problem that’s been giving me a headache for the last 2 days.
I’ll post the tables first so hopefully things will make more sense…
Player Table
| id | name | age | nationality |
|---|---|---|---|
| 1 | Bob | 20 | Italian |
| 2 | Jane | 37 | Spanish |
| 3 | Fred | 32 | French |
| 4 | Alice | 25 | Australian |
Cards Table
| id | name | level | notes |
|---|---|---|---|
| 1 | Ace of Spades | 2 | Magic Card |
| 2 | 2 of Spades | 1 | Wisdom Card |
| 3 | 3 of Spades | 11 | Wisdom Card |
| 4 | 4 of Spades | 9 | Strength Card |
| ... | ... | ... | ... |
Player_Cards Table
| player_id | card id | game_card |
|---|---|---|
| 1 | 3 | G1_C1 |
| 1 | 26 | G1_C2 |
| 1 | 19 | G1_C3 |
| 1 | 26 | G1_C4 |
| 1 | ... | ... |
| 1 | 61 | G5_C12 |
| 2 | 18 | G1_C1 |
These tables are for a (made-up for this question) game. Each player will always have 12 cards each per game and there will always be 5 games.
A player may have duplicate cards per game (e.g. 2 Ace of Spades)
The Player_Cards table is a junction table, where the ‘game_card’ field represents the game (5 in total) and the cards for each game (12 in total) – so for each ‘player_id’ in the Player_Cards table entries in the ‘game_card’ column will range from G1_C1 to G5_C12.
I'm not able to make an sql query on a single player_id where the resultant response is returned as a single row, instead, I get 60 rows (5 * 12) with player details repeated for each row e.g.
| p.name | p.nationality | p.age | c.name | game_card |
|---|---|---|---|---|
| Bob | Italian | 20 | 3 of Spades | G1_C1 |
| Bob | Italian | 20 | Jack of Hearts | G1_C2 |
| Bob | Italian | 20 | 8 of Clubs | G1_C3 |
| Bob | Italian | 20 | Jack of Hearts | G1_C4 |
| Bob | Italian | 20 | ... | ... |
| Bob | Italian | 20 | 7 of Diamonds | G5_C12 |
The results I was hoping for were along these lines...
| p.name | p.nationality | p.age | G1_C1 | G1_C2 | G1_C3 | ... | G5_C5 |
|---|---|---|---|---|---|---|---|
| Bob | Italian | 20 | 3 of Spades | Jack of Hearts | 8 of Clubs | ... | 7 of Diamonds |
I've been trying to find a solution to this, but failed miserably
Hopefully somebody can help with the required sql. Mysql doesn’t have PIVOT tables, so is there a solution without them?
I appreciate any help anyone can give
