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 |
|
spg01
Starting Member
2 Posts |
 Posted - 2008-03-13 : 15:12:22
|
| Though my problem isnt quite this simple, I will try to break it down and solve it myself from there:I have 3 tables:1) Players, with fields PlayerID and PlayerName.2) Seasons, with fields SeasonID and SeasonName.3) PlayersBySeason, with fields SeasonID, PlayerID and TotalGoals.The Players and Seasons tables have obvious keys (PlayerID and SeasonID) and The PlayersBySeason tables has a unique composite key (on SeasonID/PlayerID).My problem is this: how do I construct a sql statement that returns all the players in the Player table and how many goals they had in season X where not every player necessarily has a corresponding entry in the PlayersBySeason table (ie 'Player1' might not have played during 'Season2' and thus has no corresponding entry in the table). A simple left join doesnt work and I havent use dmuch SQL in awhile.., little help?Thanks in advance. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-13 : 15:40:19
|
| the easiest way is:select p.playerName, ps.totalGoalsfrom players pleft outer join PlayersBySeason ps on p.playerID = ps.PlayerID and s.SeasonID = @SeasonIDwhere @Season is your parameter.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
spg01
Starting Member
2 Posts |
Posted - 2008-03-13 : 15:44:01
|
| thanks, i just figured it out as i checked back in here... my problem was my 'seasonid = x' was in my where clause vs in the 'on' portion..thanks again. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
Question Regarding A Select Statement