Blog Comment Count

P

Paul Bush

I want to try and do a query of the number of comments that each user
leaves for a particular post on a user's blog site. I understand that I
would need to a CountOfID from comments table that equals the ID in
entries. My question is this, is there a way to do this in just 1
query? Maybe a visual will help....

2 tables:

entries:
id (primary key)
topic
message

comments:
id (primary key)
message
postedby


Let's say I want to use that data to produce a query that returns id,
topic, message and the countof replies. Another visual...

1 | Test Topic #1 | message #1 | 3
2 | Test Topic #2 | message #2 | 0
3 | Test Topic #3 | message #3 | 8

Thanks in advance. I will be trying this on my own as well so if I
don't get back to you right away don't be offended.

- Paul Bush
 
J

John Spencer

How is Entries related to Comments? By the message field? Or is the
Comments.Id field not a primary key, but a foreign key that identifies which
entry the comment is associated with?

ASSUMING that the tables are joined by the two message fields.

SELECT E.ID, E.Topic, E.Message, Count(C.ID) as Count
FROM Entries as E INNER JOIN Comments as C
ON E.Message = C.Message
GROUP BY E.ID, E.Topic, E.Message,
 
P

Paul Bush

ID is the primary key in entries. ID is the key in comments that
identifies. So when someone posts a comment to topic # 3 it will be
written in comments with the ID of 3.
 
P

Paul Bush

Thanks John. I actually figured it out late last night, sort of...

SELECT entries.ID, Count(comments.ID) AS CountOfComments,
entries.TOPIC, entries.MESSAGE, entries.WHATDAY, entries.WHATTIME,
entries.POSTEDBY, entries.EMAIL
FROM entries INNER JOIN comments ON entries.ID = comments.ID
GROUP BY entries.ID, entries.TOPIC, entries.MESSAGE, entries.WHATDAY,
entries.WHATTIME, entries.POSTEDBY, entries.EMAIL, comments.ID;

This return everything that I need to display. I am having a small
problem though. I have id's 11 - 15 in my database at the moment.
Only entries 11 and 14 have comments posted for them. When the query
is run I only get the fields for 11 and 14 returned to me. I tried
using an IIf function but still was returned nothing for 12, 13, and
15. Any suggestions.

IIf(CountOfComments=0, 0, CountOfComments)

Thanks again for your reply!
 
J

John Spencer

Try changing your join from an INNER JOIN to a RIGHT JOIN.
FROM entries RIGHT JOIN comments ON entries.ID = comments.ID
 
P

Paul Bush

It still only 11 and 14! Thanks though. We are close, I can feel it.
How would I go about trying an iif function? Is there a way to
accomplish the following pseudo code.

if CountOfComments IS NOTHING or Doesn't Exist Then
set CountOfComments = 0
else
CountOfComments = CountOfComments
end if
 
J

John Spencer

Strange! Obvioiusly I am missing something. One thing I did note was that
you are grouping by the comments.Id. That shouldn't be the case.

SELECT entries.ID, Count(comments.ID) AS CountOfComments,
entries.TOPIC, entries.MESSAGE, entries.WHATDAY, entries.WHATTIME,
entries.POSTEDBY, entries.EMAIL
FROM entries RIGHT JOIN comments
ON entries.ID = comments.ID
GROUP BY entries.ID, entries.TOPIC, entries.MESSAGE, entries.WHATDAY,
entries.WHATTIME, entries.POSTEDBY, entries.EMAIL

Your psedo-code id will not work as far as I know.
 
P

Paul Bush

Thanks again John. It still won't return anything for the posts that
don't have comments. What would be the correct syntax in an iif
function to determine if a value exists? At first I thought that if I
checked:

1) IIf(CountOfComments = "", 0, CountOfComments) as Expr1
2) IIf(CountOfComments < 0, 0, CountOfComments) as Expr1

1 would not work because if CountOfComments doesn't exist then the
conditional is pointless and 2 won't work because if there are no
comments then the CountOfComments for that field is nothing.

I'll try looking around for the correct syntax (if any) for finding out
if a value exists. John or anyone else that has experience or knowledge
of this matter please feel free to jump in with any information that
might lead me in the right direction.

Thanks again.
 
P

Paul Bush

Got it. It ended up being a LEFT JOIN issue. I cruised over to
iAnywhere.com and read access database join definitions. Pretty useful
stuff. Now my brain has that much more knowledge. Thanks John. This
newfound knowledge leaves me wondering why the default join is INNER...

Thanks again!
 
J

John Spencer

Correct.... I have dyslexia on right and left. When I drive and get
directions, I try to have people tell me "Driver's side" or "Passenger's
side" instead of right and left.

Sorry for the confusion and I am glad you got this solved.

Inner Join is the default because in most cases people want the matches and
only the matches. Also, if you were to select from Right and Left Joins as
a default, I don't know that one would be more prevalent than the other.
 
P

Paul Bush

One strange thing about this John...

When I run this SQL string on my ASP web page everything works fine
except for the message. I store the blog entries in a MESSAGE field
that has the MEMO attributes. The reason for this is because I don't
want to be limited to the 255 character text field. When I was pulling
all the information (before I implemented the count) the whole 255+
character message would display fine. When I use the query that
includes the count it seems that the message field only displays about
255 characters. It truncates the MESSAGE entry greatly. Have you (or
anyone else reading this) seen or heard about this strange happening?
The sql string (same as earlier) is below.

SELECT entries.ID, Count(comments.ID) AS CountOfComments,
entries.TOPIC, entries.MESSAGE, entries.WHATDAY, entries.WHATTIME,
entries.POSTEDBY, entries.EMAIL FROM entries LEFT JOIN comments ON
entries.ID = comments.ID GROUP BY entries.ID, entries.TOPIC,
entries.MESSAGE, entries.WHATDAY, entries.WHATTIME, entries.POSTEDBY,
entries.EMAIL;

I'm hoping that there is something buggy going on with the web server.
I am hoping!
 
J

John Spencer

Memo fields always get truncated to 255 characters when grouped. You can try
using First vice Grouping. I'm not sure if the FIRST function will work, but it
is an option.

There are also some other conditions that will truncate memo fields - I believe
using Distinct in the Select clause will have the same effect.

Try this modification of your query.

SELECT entries.ID, Count(comments.ID) AS CountOfComments,
entries.TOPIC,
First(entries.MESSAGE) as FullMessage,
entries.WHATDAY, entries.WHATTIME,
entries.POSTEDBY, entries.EMAIL
FROM entries LEFT JOIN comments ON
entries.ID = comments.ID
GROUP BY entries.ID, entries.TOPIC,
entries.WHATDAY, entries.WHATTIME, entries.POSTEDBY,
entries.EMAIL;
 
P

Paul Bush

Thanks a lot... again! I don't understand this FIRST business but I
guess I should look more into it.

Sorry I didn't get back sooner. My girlfriend had surgery earlier this
week and I've been playing assistant.
 
Top