Hello,
I am not very well versed at querying against Sql Server and I have been having a little trouble getting the data how I want it. Hopefully someone here is kind enough to assist. I thank you ahead of time :-)
Let me start off with my table layouts.
------------------------------------------------------
CREATE TABLE [dbo].[Competition] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (150) NOT NULL,
[Remarks] NVARCHAR (350) NULL,
[Start] DATETIME NOT NULL,
[IsActive] BIT NULL,
[End] DATETIME NOT NULL,
[Container] NVARCHAR (65) NOT NULL,
[RulesUrl] NVARCHAR(100) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Entries] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[UserId] INT NOT NULL,
[SpeciesId] INT NOT NULL,
[Length] FLOAT (53) NOT NULL,
[Remarks] NVARCHAR (350) NULL,
[Points] FLOAT (53) DEFAULT ((0)) NOT NULL,
[IsApproved] BIT NULL,
[CompetitionId] INT NOT NULL,
[CatchDate] DATETIME NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Entries_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id]),
CONSTRAINT [FK_Entries_Competition] FOREIGN KEY ([CompetitionId]) REFERENCES [dbo].[Competition] ([Id]),
CONSTRAINT [FK_Entries_SpeciesId] FOREIGN KEY ([SpeciesId]) REFERENCES [dbo].[FishSpecies] ([Id])
);
CREATE TABLE [dbo].[EntryImages] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[EntryId] INT NOT NULL,
[ImageUrl] NVARCHAR (350) NOT NULL,
[FileName] NVARCHAR (255) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_EntryImages_EntryId] FOREIGN KEY ([EntryId]) REFERENCES [dbo].[Entries] ([Id])
);
------------------------------------------------------
Then here is my query with the problem below. The problem is that multiple EntryImages can relate to a single Entry. My goal is to only select one Row for each Entry (using @EntrySelection) but when I join to EntryImages I always get back multiple rows when an Entry has multiple EntryImages.
------------------------------------------------------
Declare @EntrySelection int= 10
select top (@EntrySelection)
[dbo].Entries.Id,
[dbo].Entries.CatchDate,
[dbo].Entries.CompetitionId,
[dbo].Competition.Name,
[dbo].EntryImages.ImageUrl,
[dbo].Entries.[Length] from [dbo].Entries
inner join [dbo].Competition on [dbo].Entries.CompetitionId=[dbo].Competition.Id
inner join [dbo].EntryImages on [dbo].Entries.Id=[dbo].EntryImages.EntryId
order by [dbo].Entries.Id desc
------------------------------------------------------
Please help and thank you very much.
-Ray
Joins and limiting rows
Posted - 2015-04-02 : 19:34:01
