Joins and limiting rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joins and limiting rows

Author

Topic

rborbon
Starting Member

2 Posts

Posted - 2015-04-02 : 19:34:01

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-02 : 19:54:17

Try this:

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 (select EntryId,max(Id) as id from [dbo].EntryImages group by EntryId) as t on [dbo].Entries.Id=t.EntryId
		inner join [dbo].EntryImages on [dbo].Entries.Id=[dbo].EntryImages.EntryId t.Id=[dbo].EntryImages.Id
		order by [dbo].Entries.Id desc

rborbon
Starting Member

2 Posts

Posted - 2015-04-02 : 20:08:28

That definitely worked! Aha. Now I need to study. Thank you very much for a quick response!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources