I have a table of personns (personnID, name) and a tables of commands (CommandRef, PersonnID), several commands being associated with one personn thru the PersonnID column.
So to see my commands and personns, I do :
select name, commandRef from personns left join commands on personns.personnID = commands.PersonnID
So I get something like :
I need to have the information that a name as several commands, so I need the listing to have a third column like :
Smith Ref-1 3
Smith Ref-2 3
Smith Ref-3 3
Taylor Ref-4 1
Johns Ref-5 1
Is there a "direct" way (without using a UDF function that counts because of performance problem) to get this third column in SQL ?
Any help greatly appreciated