CODEWITHSUNDEEP
sqlt-sql
mitchellJ
mitchellJ

Reputation: 734

Table cell to count rows in other table

I'm not entirely sure if I'm even going about this in the right manner. MVC+EF site so i could do this in the controller but I would prefer it in the DB if possible.

I have two tables. One contains entries and one contains a list of members. I want the list of members to have a column that contains the count of how many times the member name appears in the list of entries. Can I do this in the definition of the table itself?

I know this query works:

select count(*)
from dbo.Entries
where dbo.Entries.AssignedTo = 'Bob Smith'

But is there any way of doing this? What is the correct syntax?

CREATE TABLE [dbo].[Members] (
[ID]    INT            IDENTITY (1, 1) NOT NULL,
[Name]  NVARCHAR (100) NOT NULL,
[Email] NVARCHAR (500) NOT NULL,
[Count] INT = select count(*) from dbo.Entries where dbo.Entries.AssignedTo = [Name]
PRIMARY KEY CLUSTERED ([ID] ASC)

I've done some searching and have tried a few different syntax's but I'm completely lost at this point so if anyone can get me headed in the correct direction I would really appreciate it.

Thanks in advance.

Upvotes: 0

Views: 44

Answers (1)

heptadecagram
heptadecagram

Reputation: 898

You could create Members as a view combining both the Entries data and another table. (Warning: Syntax not tested)

CREATE TABLE [_member_data] (
  [ID]   INT IDENTITY(1, 1) NOT NULL,
  [Name]  NVARCHAR (100) NOT NULL,
  [Email] NVARCHAR (500) NOT NULL,
  PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE VIEW [dbo].[Members] AS
  SELECT ID, Name, Email, COUNT(*)
    FROM _member_data JOIN dbo.Entries ON [Name] = [AssignedTo]
    GROUP BY 1, 2, 3;

It is possible to take this even further with triggers/rules that rewrite attempted inserts into Members as inserts to the appropriate backing table. But to get the kind of expressive information that you are looking for, you really want to explore using a view.

Upvotes: 3

Related Questions