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.
|1||Ace of Spades||2||Wisdom Card|
|2||2 of Spades||1||Wisdom Card|
|3||3 of Spades||3||Wisdom Card|
|4||4 of Spades||1||Strength Card|
|5||5 of Spades||1||Magic Card|
|6||6 of Spades||2||Magic Card|
|7||7 of Spades||1||Strength Card|
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.
Now for the problem…so far, when asking for Bob’s details (I only want a single player each request) my SQL efforts resulted in something like below (truncated so it doesn't take up too much room)…
which results in 60 (5x12) rows for each player, with the player details duplicated each row
When the results I wanted are…
|1||Bob||20||Italian||3 of Spades||3 of Spades||Jack of Hearts||….||King of Diamonds|
I realise this isn’t a mysql forum, but it seemed the most knowledgeable and friendly forums of all the sql forums I looked at, so 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