Help! If anyone can help me out with this I will severely owe you one. I have this SQL query that I need to return just one more field, and I can’t figure out how to do it. (The query is in the extended text for this entry; click on the icon or the comments link to get there!) Currently the query needs the parameter [TeamID] to run, and it returns the Sum of the Wins and Losses for the [TeamID]. (The Sum of the Wins is the Sum of the AwayWins and the Sum of the HomeWins. And the Sum of the Losses is the same except for Losses instead of Wins …) What I want to be able to do is to have it return the [TeamID] as well, so that I can build another query on top of this one. Anyone? Anyone? Please eMail me if you have any thoughts …
SELECT (SELECT [AwayLosses].[CountOfGameID]+[HomeLosses].[CountOfGameID] AS Losses FROM (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.AwayID=[TeamID] AND [tblSeason].[AwayScore] < [tblSeason].[HomeScore]) AS AwayLosses, (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.HomeID=[TeamID] AND tblSeason.HomeScore < tblSeason.AwayScore) AS HomeLosses;) AS TotalLosses, (SELECT HomeWins.CountOfGameID+AwayWins.CountOfGameID AS Wins FROM (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.HomeID=[TeamID] And [HomeScore] > tblSeason.[AwayScore]) AS HomeWins, (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.AwayID=[TeamID] AND tblSeason.AwayScore > tblSeason.HomeScore) AS AwayWins) AS TotalWins
FROM (SELECT AwayLosses.CountOfGameID, HomeLosses.CountOfGameID FROM (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.HomeID=[TeamID] AND tblSeason.HomeScore < tblseason .AwayScore) AS HomeLosses, (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.AwayID=[TeamID] AND [tblSeason].[AwayScore] < [tblSeason].[HomeScore]) AS AwayLosses) AS Losses, (SELECT AwayWins.CountOfGameID, HomeWins.CountOfGameID FROM (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.AwayID=[TeamID] AND tblSeason.AwayScore > tblSeason.HomeScore) AS AwayWins, (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.HomeID=[TeamID] And [HomeScore] > tblSeason.[AwayScore]) AS HomeWins) AS Wins
Actually now that I think about it: If anyone has any ideas on a better database structure, I’m open to that too. Right now there’s tblSeason which has [GameID] as a primary key, [AwayID], [HomeID] as the links to tblTeams, [HomeScore] and [AwayScore] storing the scores for each game, and a [WeekID] which is a link to tblWeeks.
Can you send me the database? Can’t promise you anything but I may be able to talk a couple of friends of mine into looking at it. What’s the finders fee for a solution or are you heavily into that “open source” thing? 🙂