Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
seabass3000
Starting Member
4 Posts |
 Posted - 2011-01-13 : 00:05:31
|
| Hi there, I'm having an issue trying to get my head around how to do the following. Basically I have two tables, the first lists details of players in a sports team, with each player given a unique ID, the relevant fields are shown below:'players' tablePlayerID Surname-------- ----------001 Jones002 Smith003 Wilkins004 Morgans005 Wyatt...And so onMy second table lists the players that have been selected to play in a given match, using their PlayerID's from the table above. Some example data is shown below:'selection' tableMatchID Player1 Player2 Player3 ...------- ------- ------- -------0321 004 001 005I want to write a query that gives me a list of surnames of players selected to play in a given match. So for the example data shown, for matchID 0321 I would want to see:MorgansJonesWyattI can't think of a neat way of doing this as I'm fairly new to SQL. The only way I can think to do it is to write each surname individually to a temporary table and then display the results from there, but that seems very long winded. Any help would be hugely appreciated. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-13 : 03:38:30
|
How many player-columns do you have in selection table?I would have a table selection with MatchID and PlayerId.In this table then there are entries for each Player in a game.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
seabass3000
Starting Member
4 Posts |
Posted - 2011-01-13 : 05:17:37
|
| Thanks very much for your response. There are a total of 22 player columns, so the entire selection table fields are:MatchIDP1P2P3P4P5P6P7P8P9P10P11P12P13P14P15S1S2S3S4S5S6S7Px denotes player positions that must be selected (NULLS not allowed), Sx denotes possible substitutes (NULLS allowed). To do what your suggesting would I have to create an index between each of the P and S fields above with the PlayerID field in the players table? And if so how still how would I query it in the way described in the OP?Regards |
|
|
|
seabass3000
Starting Member
4 Posts |
Posted - 2011-01-13 : 05:26:36
|
| Apologies, on re-reading your post I see what you mean. So you would have entries in the selection table such as:MatchID PlayerID------- --------0321 0040321 0050321 001Im still intrigued how I would query this to create my list of surnames for the given MatchID though? (i.e. a query that reads the player ID's for the given match, but converts them to their player surnames before returning the result). Also, in the long run this solution will not work for me as I need to keep the distinction between different player positions. In other words it matters for a given game who was P1, who was P2, who was S1 (i.e. a substitute rather than a starting player) etc.Someone mentioned CURSORs to me as a way of looping through rows of a table within a single query. Could this possibly help me by allowing me to loop through each player for a match and converting to its surname?Any more help would be hugely appreciated. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-13 : 05:47:40
|
[code]-- Creating sample data for playersdeclare @players table (PlayerID int, Surname varchar(255))insert @playersselect 1, 'Jones' union allselect 2, 'Smith' union allselect 3, 'Wilkins' union allselect 4, 'Morgans' union allselect 5, 'Wyatt'-- Creating sample data for selectiondeclare @selection table (MatchID int, Player1 int, Player2 int, Player3 int)insert @selectionselect 321, 4, 1, 5-- I want to write a query that gives me a list of surnames of players -- selected to play in a given match. -- So for the example data shown, for matchID 0321 I would want to see:-- Morgans-- Jones-- Wyatt-- Solution using UNPIVOTselect s.MatchID, s.PlayerID, s.PlayerNo,pl.Surnamefrom (select MatchID, Player1, Player2, Player3 from @selection) as punpivot (PlayerId for PlayerNo in (Player1, Player2, Player3)) as sjoin @players as pl on pl.PlayerID = s.PlayerId[/code]
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
seabass3000
Starting Member
4 Posts |
Posted - 2011-01-13 : 10:02:42
|
| Thanks again for your response. Unfortunately I can't get your query to run, everytime I try I'm told 'Syntax error in FROM clause.', has anyone got any ideas why this might be? Is there possibly any confusion between the field names used? I noticed a PersonNO, a PersonID and a PersonId, are these all meant to be the same thing?Cheers |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-13 : 10:28:16
|
Please show the query that is giving the error.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
Combining data from two tables...