CODEWITHSUNDEEP
sqlvb.netvisual-studio
Neal
Neal

Reputation: 673

Retrieve count of related records

I currently have two tables in my data source that I'm referencing in this instance. Firstly, to explain the context, I have a windows form program in VB.NET (Visual Studio 2013).

In the first table Trainer, I have the following fields : ID, First Name, Surname, Contact, Class.

In the second table Member, I have the following fields : ID, First Name, Surname, Contact, Type, TrainerID.

I have enforced referential integrity between the two tables using Trainer.ID as PK and Member.TrainerID as FK with a 1:m relationship. I'm trying to retrieve the count of related records, to the specified ID of the trainer. I want to retrieve the count of related records. So, for example on the form I click Search and provide a trainer ID, I'd like to return the amount of customers he/she has belonging to them.

I need that, so that I can work out their salary based on commission + base amount. I've looked around a lot, read up a lot but I just can't seem to get it. Any help whatsoever will be appreciated.

Upvotes: 0

Views: 81

Answers (2)

juergen d
juergen d

Reputation: 204884

select count(m.id) as count_members
from trainer t
left join member m on m.trainerid = t.id
where t.surname = 'watson'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270623

If you have the trainer id, can't you just do:

select count(*) as cnt
from member m
where m.trainerid = @TheTrainerId;

Upvotes: 2

Related Questions