I'm creating a site for a national league and am having difficulty querying for a particular type of statistic that I'm hoping an expert on here can help me with 
My data structure is such that an Event is a league meeting (all games take place on the same day) which has Fixtures. These Fixtures have Fixture_Events (essentially someone scoring a goal, a timeout being called, a penalty being awarded). I also have a Teams table, a Players table, a TeamRoster table (all players registered to a team) and a FixtureAttendees table (players from team who played in game). I'm trying to return 2 statistics.The first is "How many Shutouts has a goalie had?"The rule for this in English, how many games has a player participated in, where they have played in goals, and the score has been x-0 in their favour.The second is "How many game winning goals has a player scored?"The rule for this is essentially, how many goals has a player scored where the game was previously tied (e.g. 2-2) and they have scored the last goal in the fixture (e.g. 3-2).My tablesCREATE TABLE [dbo].[Fixture_Events]( [FixtureEventID] [int] IDENTITY(1,1) NOT NULL, [FixtureID] [int] NOT NULL, [EventType] [nvarchar](50) NOT NULL, [EventTime] [nchar](5) NOT NULL, [TeamID] [int] NOT NULL, [Player1] [int] NOT NULL, [Player2] [int] NULL, [EventCode] [nvarchar](50) NULL, [PenaltyMinutes] [int] NULL, CONSTRAINT [PK_Fixture_Events] PRIMARY KEY CLUSTERED ( [FixtureEventID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[Events_Fixtures]( [FixtureID] [int] IDENTITY(1,1) NOT NULL, [EventID] [int] NOT NULL, [LocationID] [int] NOT NULL, [FixtureDate] [smalldatetime] NOT NULL, [HomeTeam] [int] NOT NULL, [AwayTeam] [int] NOT NULL, CONSTRAINT [PK_Seasons_Fixtures] PRIMARY KEY CLUSTERED ( [FixtureID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[Fixture_Attendees]( [FixtureID] [int] NOT NULL, [PlayerID] [int] NOT NULL, [Goalkeeper] [bit] NOT NULL CONSTRAINT [DF_Fixture_Attendees_Goalkeeper] DEFAULT ((0)), CONSTRAINT [PK_Fixture_Attendees] PRIMARY KEY CLUSTERED ( [FixtureID] ASC, [PlayerID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[Fixture_Attendees]( [FixtureID] [int] NOT NULL, [PlayerID] [int] NOT NULL, [Goalkeeper] [bit] NOT NULL CONSTRAINT [DF_Fixture_Attendees_Goalkeeper] DEFAULT ((0)), CONSTRAINT [PK_Fixture_Attendees] PRIMARY KEY CLUSTERED ( [FixtureID] ASC, [PlayerID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
It's a complicated database as I'm tried to model it as accurately as I can. It may actually be easier if I made a backup of the database available rather than trying to post code.If anybody can help me and required further info, please let me know.Thanks in advance.
Complex Query
Posted - 2007-12-04 : 14:07:45