Hi Bitsand I am trying to apply what you showed me to multiple tables but I am have problems getting it to work. I have four Tables Performance, Athletes, Events and Teams.
The structure of my tables are:
CREATE TABLE [dbo].[Athletes] (
[AthleteID] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (20) NULL,
[LastName] NVARCHAR (20) NULL,
[BirthDate] DATE NULL,
[Gender] NCHAR (10) NULL,
[Email] NVARCHAR (50) NULL,
[Street] NVARCHAR (50) NULL,
[Town] NVARCHAR (15) NULL,
[Parish] NVARCHAR (15) NULL,
[Phone] NVARCHAR (20) NULL,
CONSTRAINT [PK_Athletes] PRIMARY KEY CLUSTERED ([AthleteID] ASC)
);
CREATE TABLE [dbo].[Events] (
[EventID] INT IDENTITY (1, 1) NOT NULL,
[EventName] NVARCHAR (50) NULL,
[GroupID] INT NULL,
[EventCode] NVARCHAR (30) NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ([EventID] ASC),
CONSTRAINT [FK_Events_EventGroups] FOREIGN KEY ([GroupID]) REFERENCES [dbo].[EventGroups] ([GroupID])
);
CREATE TABLE [dbo].[Performance] (
[PerformanceID] INT IDENTITY (1, 1) NOT NULL,
[EventID] INT NULL,
[AthleteID] INT NULL,
[PerformanceDate] DATE NULL,
[TeamID] INT NULL,
[MeetID] INT NULL,
[Mark] FLOAT (53) NULL,
[Venue] NVARCHAR (200) NULL,
[Wind] NCHAR (20) NULL,
CONSTRAINT [PK_Performance] PRIMARY KEY CLUSTERED ([PerformanceID] ASC),
CONSTRAINT [FK_Performance_Athletes] FOREIGN KEY ([AthleteID]) REFERENCES [dbo].[Athletes] ([AthleteID]),
CONSTRAINT [FK_Performance_Events] FOREIGN KEY ([EventID]) REFERENCES [dbo].[Events] ([EventID]),
CONSTRAINT [FK_Performance_Meets] FOREIGN KEY ([MeetID]) REFERENCES [dbo].[Meets] ([MeetID]),
CONSTRAINT [FK_Performance_Teams] FOREIGN KEY ([TeamID]) REFERENCES [dbo].[Teams] ([TeamID])
);
CREATE TABLE [dbo].[Teams] (
[TeamID] INT IDENTITY (1, 1) NOT NULL,
[TeamName] NVARCHAR (50) NULL,
[TeamCode] NVARCHAR (50) NULL,
CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED ([TeamID] ASC)
);
I would like to use the AthleteID, TeamID, EventID from the Performance table to select the FirstName, Last Name From Athletes Table, EventName from Events Table, TeamName From Team Table Mark, Wind and venue from the performance table Then Apply the ranking that you did for me but I am having problems with the query. Can you help with that?
The rankings Should list.
The following columns
Ranking, Mark, Wind, Event, Date, Venue
Any Help will be greatly appreciated.