Count problems

[alexwm20]

I have sqldatasources that are used to display information in a gridview. The data that is being collected comes from 3 different tables. From the one table called comments all i want is a count of all comment table rows that have a common id with a row in the files table. The select query i have right now only returns results that have a comment count of more than 0, i want entries with a comment count of 0 to be found... here is what i have so far (i am a sql noob :( )

SELECT dbo.Files.FID, dbo.Files.UID, dbo.Files.FileN ame, dbo.Files.Date, dbo.Files.tType , dbo.Files.numPo ints, dbo.Files.numDl ds, dbo.Files.Confi rmation, dbo.Users.UID AS Expr1, dbo.Users.Name, dbo.Users.Alias , COUNT(dbo.Comme nts.FID) AS comcount

FROM dbo.Files INNER JOIN dbo.Users ON dbo.Files.UID = dbo.Users.UID INNER JOIN dbo.Comments ON dbo.Files.FID = dbo.Comments.FI D

WHERE (dbo.Files.Comp any = @Company)

GROUP BY dbo.Files.FID, dbo.Files.UID, dbo.Files.FileN ame, dbo.Files.Date, dbo.Files.tType , dbo.Files.numPo ints, dbo.Files.numDl ds, dbo.Files.Confi rmation, dbo.Users.UID, dbo.Users.Name, dbo.Users.Alias

[Roobmeister]

There is probably an easier way to do this but try this.. I think it might work..


SELECT dbo.Files.FID, dbo.Files.UID, dbo.Files.FileN ame, dbo.Files.Date, dbo.Files.tType , dbo.Files.numPo ints, dbo.Files.numDl ds, dbo.Files.Confi rmation, dbo.Users.UID AS Expr1, dbo.Users.Name, dbo.Users.Alias ,
SUM(CASE WHEN dbo.Comments.FI D IS NOT NULL THEN 1 ELSE 0 END) AS comcount

FROM dbo.Files
LEFT JOIN dbo.Users ON dbo.Files.UID = dbo.Users.UID
LEFT JOIN dbo.Comments ON dbo.Files.FID = dbo.Comments.FI D

WHERE (dbo.Files.Comp any = @Company)

GROUP BY dbo.Files.FID, dbo.Files.UID, dbo.Files.FileN ame, dbo.Files.Date, dbo.Files.tType , dbo.Files.numPo ints, dbo.Files.numDl ds, dbo.Files.Confi rmation, dbo.Users.UID, dbo.Users.Name, dbo.Users.Alias