CODEWITHSUNDEEP
c#asp.netsqlsql-server
smtnkc
smtnkc

Reputation: 508

Comment Count on Entry Table (SQL Query)

It may be a simple/specific question but I really need help on that. I have two tables: Entry and Comment in a SQL Server database. I want to show comment count in entry table. And of course comment count will increase when a comment is added. Two tables are connected like this: Comment.EntryId = Entry.Id

Entry table:

CREATE TABLE [dbo].[Entry] (
    [Id]                  INT            IDENTITY (1, 1) NOT NULL,
    [Subject]             NVARCHAR (MAX) NOT NULL,
    [Content]             NVARCHAR (MAX) NOT NULL,
    [Type]                NVARCHAR (50)  NOT NULL,
    [SenderId]            NVARCHAR (50)  NOT NULL,
    [Date]                DATE           NOT NULL,
    [Department]          NVARCHAR (50)  NULL,
    [Faculty]             NVARCHAR (50)  NULL,
    [ViewCount]           INT            DEFAULT ((0)) NOT NULL,
    [SupportCount]        INT            DEFAULT ((0)) NOT NULL,
    [CommentCount]        INT            DEFAULT ((0)) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Comment table:

CREATE TABLE [dbo].[Comment] (
    [Id]               INT            IDENTITY (1, 1) NOT NULL,
    [EntryId]          INT            NOT NULL,
    [SenderId]         NVARCHAR (50)  NOT NULL,
    [Date]             DATETIME       NOT NULL,
    [Content]          NVARCHAR (MAX) NOT NULL,
    [SupportCount]     INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

I am showing the entries in a gridview in codebehind (c#). The question is this, what should I write as a query to do this most efficiently? Thanks for help.

Upvotes: 0

Views: 159

Answers (2)

Sparky
Sparky

Reputation: 15105

Try this:

select e.Id,e.date,count(*) as NumComments
from Entry e
join comment c on c.entryId=e.id
group by e.id,e.date

If there might be no comments, try the following

select e.Id,e.date,count(c.entryId) as NumComments
from Entry e
left join comment c on c.entryId=e.id
group by e.id,e.date

Upvotes: 3

syed mohsin
syed mohsin

Reputation: 2938

You can use left join for that purpose. Kindly me more specific with what fields you want in gridview

And why do you want commentcount in table (most tables have that 1-many relation and we didn't use that). If you keep that in table you have to update entry table every time when comment is made.

Upvotes: 0

Related Questions