[RESOLVED] Column that counts occurrences of FK in another table

[wolf99]

Hi Folks

Im pretty new to SQL and am trying to get to grips with it with a working SQL server DB for use with a VB.NET windows form application
(dont have the chops to set up a test server one nor time to get the chops before delivering applications)

I have my tables and columns set up for the most part, using the visual designer (these tables are not live, btw).

Two of my tables are related in that they both describe a test program

The first table, dbo.Program, currently just has two columns; ID (PK, int, not null) and ProgramName (varchar(50), not null)

The second table, dbo.ProgramStep, has 6 columns, though only a few will be of interest for this question; ID (PK, int, not null), ProgramId (FK, int, not null) and ProgramStep (int, not null). The FK is a one-to-many from the dbo.Program.ID

This means that the first table acts as an index to "sets" of steps within the second table.

I.E: a program has an ID and a name, that ID provides a relationship to the set of ProgramSteps that make up the Program.

What I would like is to add a column to the dbo.Program table that shows the total number of steps in that program in the table dbo.ProgramStep...
If possible I would like that column to automatically update itself as this would mean it would be totally independent of any application that accessed the data.

Im not sure where to even start with this though...

I guess it doesnt actually have to be a column, just a way of accessing the data that is seamless to the application, so maybe a stored procedure? with some kind of trigger?

Any help at all much appreciated

[dee-u]

I think what you need is just a plain SQL which will COUNT how many child records belongs to a parent record, I don't think adding another column for that is necessary.

[wolf99]

OK, thanks.

The reason i was asking is that I have some code that runs a query on the Program table to get the ID and ProgramName. With the setup as you suggest (and what I currently have) another query is needed, of the ProgramStep table to get the number of steps.

I thought if there was a column with this info already in a column in the Program table it would negate the need for a second query...