How to calculate relationships from one to many

Question

ReporterTbl has a one-to-many relationship with AttachmentTbl .

In ReporterTbl I have ID (101) and I can have AttachmentTbl more than one Attachment associated with ReporterTbl.Id

 SELECT ISNULL(ReporterTbl.Id, 0) AS Id, CONVERT(char(10), ReporterTbl.StartDate, 101) AS StartDate, ISNULL(ReporterTbl.PriorityId, 0) AS PriorityId, ISNULL(dbo.ReporterTbl.PriorityDesc, '') AS PriorityDesc, (select ReporterTbl.Id, COUNT(dbo.AttachmentTbl.Id) AS attachment_Id FROM dbo.AttachmentTbl RIGHT OUTER JOIN ReporterTbl ON dbo.AttachmentTbl.Id = ReporterTbl.Id GROUP BY ReporterTbl.Id) AS IsAttachment ) 

Basically, what I'm trying to find out is given by ReporterTbl.Id , how many Attachment do I have?

Table structure:

  ReporterTbl Id int {**PrimaryKey**} StartDate datetime PriorityId int PriorityDesc varchar(500 AttachmentTbl: AttachmentId indentity Id {**FK to ReproterTbl**} Filename Content ... 

Answer 1

 select r.id, count(a.id) as Count from ReporterTbl r left outer join AttachmentTbl a on r.id = a.id group by r.id 

Answer 2

If you want to get all fields from the Report (not just ID ), this will save you from JOIN :

 SELECT r.*, ( SELECT COUNT(*) FROM AttachmentTbl a WHERE a.id = r.id ) AS AttachmentCount FROM ReportedTbl r 

Answer 3

considering ReporterTbl.ID how many attachments do I have.

Is not it:

 select count(*) from AttachmentTbl where id = @ID;