Results in a single row

1

Hello,
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.

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 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
…. …. …. ….


Player_Cards Table
player_id card id game_card
1 3 G1_C1
1 3 G1_C2
1 24 G1_C3
1 … G5_12
2 41 G1_C1
2 … …
2 35 G3_C1
2 3 G3_C1
2 35 G3_C1
2 … G5_C12


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

player_id card id game_card
1 3 G1_C1
1 3 G1_C2
1 24 G1_C3
1 … G5_12
2 41 G1_C1
2 … …
2 35 G3_C1
2 3 G3_C1
2 35 G3_C1
2 … G5_C12

which results in 60 (5x12) rows for each player, with the player details duplicated each row

When the results I wanted are…

id name age nationality G1_C1 G1_C2 G1_C3 … G5_C12
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

3

hi

hope this helps

i did it using DYNAMIC Pivot

but you can use
LookUp Tables
where you physically map the things and join

just an idea

image

1 Like
4 
SELECT P.id, P.name, P.age, P.nationality
	,x.G1_C1, x.G1_C2
	...
	,x.G5_C12
FROM Players P
	JOIN
	(
		SELECT PC.player_id
			,MAX(CASE WHEN PC.game_card = 'G1_C1' THEN C.name ELSE '' END) AS G1_C1
			,MAX(CASE WHEN PC.game_card = 'G1_C2' THEN C.name ELSE '' END) AS G1_C2
			...
			,MAX(CASE WHEN PC.game_card = 'G5_C12' THEN C.name ELSE '' END) AS G5_C12
		FROM Player_Cards PC
			JOIN Cards C
				ON PC.card_id = C.id
		GROUP BY PC.player_id
	) X
	ON P.id = X.player_id
WHERE P.id = 1;

ps I know this is a made up example but age should not be held in a db. ie Always calculate age from a date held in the db.

1 Like
5

Thanks harisgg1,

I ended up using ifor's solution, but I did look into LookUp Tables after seeing your reply, which is going to be helpful for some other sql I have to do - so many thanks for that

6

Fantastic! - thank you Ifor

I've implemented your solution and it works a treat - it gives me exactly what I needed

You're right about using 'Age' of course - it was a field made up by an addled brain for the purpose of the question.

Thanks once again, it's very much appreciated - I'll sleep better tonight