Computed SQL Server 2005 column result from a collection of other table field values

Question

Sorry for the long title of the question.

I guess I'm on a loser at this, but by accident.

Is it possible to make the calculation of a calculated field in a table the result of an aggregated function applied to a field in another table.

i.e.

You have a table called a "mug", she has a child called "color" (which makes my head in the UK sick, but the seller from the USA, what are you going to do?), And this, in turn, has a child called "the size". Each table has a field called sold.

Size size increases by 1 for each mug of a certain color and size.

You want color.sold to constitute the aggregate size SUM.sold WHERE size.colorid = color.colorid

You want mug.sold to be a collection of SUM color.sold WHERE color.mugid = mug.mugid

In any case, to make mug.sold and color.sold just work on their own, or will I have to leave with triggers?

Answer 1

you cannot have a computed column directly reference another table, but you can reference a user-defined function. here is a link to an example of the implementation of such a solution.

http://www.sqlservercentral.com/articles/User-Defined+functions/complexcomputedcolumns/2397/

Answer 2

No, this is impossible to do. The calculated column can only be obtained from the values ​​of other fields in the same row. To calculate a population from another table, you need to create a view.

If your application should show statistics, ask the following questions:

• Does it really need to be shown in real time? If so, why? If you really need to do this, you will have to use triggers to update the table. This refers to a short article on denormalization on Wikipedia. Triggers will affect write performance when updating a table and rely on active triggers.
• If it is necessary only for reporting purposes, you can perform the calculation in a view or report.
• If you need to maintain frequent advertising, you may end up in the data processing and ETL process overnight.