Value of a column from counting records in another table

[marthirial]

SQL Server

I have two tables, one is "description" and the second is "comments" this two tables are related in the way that "description" can have many "comments".

I want to have a column in the table "description" that holds the count of "comments" belonging to such table, so I can display it as the count later on the page.

I have limited knowledge of Store Procedures or scripts in SQL , so an script would be greate for me to adapt.

Thanks

 

[amrita418]

Personally, I would not suggest storing the count in the description table.

For every change in the comments table, the count needs to be updated, which is a clear overkill.

Instead create a view that gets you the count with other columns from description.
something like:

create view viewname as
select a.*, b.value
from   description a
       inner join 
       (select   keyfield,    
                 count(*) value
        from     comments 
        group by keyfield) b
       on (a.keyfield = b.keyfield)

Regards,
AA

 

[marthirial]

Thanks AA, the script worked just fine.