Getting Values From Other Tables

Howdy all. I've tried to figure this out on my own, but alas!, I keep hitting a brick wall.


I'm making a game score "keep-tracker" (very technical jargon, I know...) and so I've set up 3 tables:





Table 1: Players


Columns: PlayerID, PlayerName, Paid





Table 2: Contestants


Columns: ContestantID, ContestantName, Eliminated





Table 3: Picks


Columns: PlayerID, Pick1, Pick2, Pick3, etc...





For Players and Contestants tables, the IDs are int, the names are nvarhars, and Paid/Eliminated is boolean (bit).





Now, I'm trying to display in a datagrid using ASP.NET 1.1, the PlayerName along with the Picks that they have chosen.





I've created a stored proc as follows:





CREATE PROCEDURE GetPicks AS


SELECT


Players.PlayerName,


Picks.Pick1,


Picks.Pick2,


Picks.Pick3,


Picks.Pick4


FROM


Picks, Players, Contestants


WHERE


Picks.PlayerID = Players.PlayerID AND Picks.Pick1 = Contestants.ContestantID


ORDER BY


PlayerName


GO











The result I'm getting is:





PlayerName Pick1 Pick2 Pick3 Pick4


Bob 1 3 5 12


Charlie 1 3 16 17


Wilma 4 7 9 14








The PlayerName appears, rather than PlayerID, which is good to see, but my question is: how can I get the ContestantName to appear as Pick1, Pick2, etc., instead of the integer?





I've tinkered around with JOINs, but I'm a beginner and I'm finding JOINs a bit confusing...


Help would be appreciated! Thanks for your help in advance...





JP