Define a link reference to another table column

Question

I have two database tables, Team ( ID, NAME, CITY, BOSS, TOTALPLAYER ) and Player ( ID, NAME, TEAMID, AGE ), the relationship between the two tables is one for many, one team can have many players.

I want to know if there is a way to define the TOTALPLAYER column in the Team table as calculated?

For example, if 10 TEAMID players TEAMID equal to 1, then the row in the Team table, which ID is 1, has a TOTALPLAYER column with a value of 10. If I add a player, the value of the TOTALPLAYER column increases to 11, I do not need to explicitly assign a value to it, let it be generated database. Does anyone know how to implement this?

thanks in advance.

BTW Database - SQL Server 2008 R2

Answer 1

Yes, you can do this - you need a function to count the players for the team and use it in the calculated column:

 CREATE FUNCTION dbo.CountPlayers (@TeamID INT) RETURNS INT AS BEGIN DECLARE @PlayerCount INT SELECT @PlayerCount = COUNT(*) FROM dbo.Player WHERE TeamID = @TeamID RETURN @PlayerCount END 

and then define your computed column:

 ALTER TABLE dbo.Team ADD TotalPlayers AS dbo.CountPlayers(ID) 

Now, if you choose, this function is called every time, for each selected command. The value is not stored in the Team table - it is calculated on the fly every time you select Team from the table.

Since this value is not saved, the question actually arises: does it need a calculated column in the table, or can you just use the stored function to calculate the number of players, if necessary?

Answer 2

You do not need to save the total in the table - it can be calculated when executing the query, for example:

 SELECT teams.*, COUNT(players.id) AS num_players FROM teams LEFT JOIN players ON teams.id = players.team_id GROUP BY teams.id; 

This will create an additional column "num_players" in the query, which will count the number of players in each team, if any.