SQL

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


There are 2 comments on this post

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

  2. 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? 🙂

Add to the discussion:

I'll never share your email address and it won't be published.

What Is This?

davidgagne.net is the personal weblog of me, David Vincent Gagne. I've been publishing here since 1999, which makes this one of the oldest continuously-updated websites on the Internet.

bartender.live

A few years ago I was trying to determine what cocktails I could make with the alcohol I had at home. I searched the App Store but couldn't find an app that would let me do that, so I built one.

Hemingway

You can read dozens of essays and articles and find hundreds of links to other sites with stories and information about Ernest Hemingway in The Hemingway Collection.