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.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID AS Expr1, dbo.Users.Name, dbo.Users.Alias, COUNT(dbo.Comments.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.FID

WHERE (dbo.Files.Company = @Company)

GROUP BY dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, 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.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID AS Expr1, dbo.Users.Name, dbo.Users.Alias,
SUM(CASE WHEN dbo.Comments.FID 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.FID

WHERE (dbo.Files.Company = @Company)

GROUP BY dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID, dbo.Users.Name, dbo.Users.Alias