I have the following query:

SELECT TOP 5 *
FROM dbo.Weblog
ORDER BY blogDate DESC

I have another table "UserComments" with a foreign key field "blogID" related to the primary key field "blogID" in the table "Weblog".

I'm trying to alter the above query to count all the records related by the "blogID" field from both tables.

I want to return the total record count for all user comments for a specific "blogID" field in the Weblog table.

For example, if the blogID field in the Weblog table is 22 then I want to return the count for all records that contain 22 in the "blogID" field in the UserComments table.

I was trying this:
SELECT TOP 5 * , (SELECT COUNT(*) FROM dbo.UserComments) AS TotalRecords
FROM dbo.Weblog
ORDER BY blogDate DESC

which returned all the records in the UserComments table instead of the count related by the "blogID" field.

How can I return the correct count by specific "blogID" instead all the records in the UserComments table?

Thanks for any help.
-Dman100-

Hello,

Try this :

SELECT dbo.UserComments.blogID, count (*)
FROM dbo.UserComments, dbo.Weblog
WHERE dbo.UserComments.blogID = dbo.Weblog.blogID
GROUP BY dbo.UserComments.blogID;

Regards,

RBARAER

use a left outer join so that you get blogs without comments too
note that you cannot say "select star" when you use GROUP BY

Sorry, but in Oracle that works perfectly. Maybe I'm becoming too much Oracle-SQL-Oriented... Is it not standard ANSI SQL ?

Thanks for the reply! Using the LEFT OUTER JOIN is getting very close, but I am unable to include the field "blogComment" in the query because the data type is set to text in the database. Is there a way around this?

This is what I have so far:

SELECT TOP 5
W.blogDate,
W.blogHeader,
W.blogPostTime,
COUNT(C.blogID) AS Comments
FROM dbo.tblWeblog AS W
LEFT OUTER
JOIN dbo.tblUserComments AS C
ON W.blogID = C.blogID
GROUP BY
W.blogDate,
W.blogHeader,
W.blogPostTime
ORDER BY
W.blogDate DESC

So, if I include the filed "blogComment" then I get an error related to the text data type.

SELECT TOP 5
W.blogDate,
W.blogHeader,
W.blogComment,
W.blogPostTime,
COUNT(C.blogID) AS Comments
FROM dbo.tblWeblog AS W
LEFT OUTER
JOIN dbo.tblUserComments AS C
ON W.blogID = C.blogID
GROUP BY
W.blogDate,
W.blogHeader,
W.blogComment,
W.blogPostTime
ORDER BY
W.blogDate DESC

Thanks again for your help!
-Dman100-

okay, you're right, let me be more specific

if you use GROUP BY, you must itemize the columns to group on

furthermore, since you must group on all non-aggregate columns in the SELECT list, you might as well write them out there as well

typically, "select star" gets you into way more trouble than it's worth

and with most people using a text editor (rather than command line input), it becomes trivial to copy/paste the list of columns that you want

i mean, since you have to itemize them in the GROUP BY anyway

the solution to this problem is that you don't want to

if it's a TEXT field, and you want 5 of them, are you really going to display all 5 fields? no

so use left(W.blogComment,50) as teaser or something

Do you only mean it is cleaner to specify columns on which one does the count instead of using * ? I would say it depends on the type of count one wants to do, isn't it ? If one wants to use a DISTINCT count on specific fields, then OK, but if one wants to count ALL rows for which (field1, field2) is the same, then why not write :

select field1, field2, count(*) from table1 group by field1, field2;

?

Excuse me, but I don't see the "trouble" you're talking about when using count(*). Could you please give me a precise example ?

Regards,

RBARAER

what i meant by "select star" is SELECT *

in this specific example, you should not say because, as we discovered, the "star" includes a TEXT field which causes an error ("gets you into trouble") even if you do also code it into the GROUP BY

as to your other question, count(*) gives you the wrong answer when you are counting in a left outer join and there are no matched records

Sorry for having misunderstood your point, r937. I agree with you on the "select *". Anyway, this talk will have taught me something : I must admit I'm not quite used to using OUTER JOIN, that's why I was not seeing any trouble in using count(*). Now I see.

Thank you and best regards,

RBARAER