469,898 Members | 1,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

Count problems

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
Dec 3 '07 #1
1 1189
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
Dec 4 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

22 posts views Thread by Ling Lee | last post: by
3 posts views Thread by Igor Kryltsov | last post: by
8 posts views Thread by novus | last post: by
68 posts views Thread by Martin Joergensen | last post: by
1 post views Thread by ank | last post: by
22 posts views Thread by MP | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.