Hi All, anyone help me with this one..!?

I have a page that displays information from a table something like a blog. It has small little articles on it.

Like this

-----------
Title
Paragraph or two
Comments

Title
Paragraph or two
Comments
---------------

"Comments" is a link they can click to view and add comments for that particular article. The comments are stored in another table. I was asked if I could show the number of current comments for each article,

i.e. Comments (2)

I am using a select to present the information with While Not and MoveNext to display all the articles.

What is the best way to list the amount of comments for each. I am supposing I will use a COUNT() but not quite sure if I will insert another Select statement or use a JOIN or what.

Any direction would be much appreciated.

Thanks,

Chip

I do two queries for the one I'm coding, one to load the article/post, second to load the comments

Thanks Chris,

Do I nest the query for the comments inside of the while statement pulling the id from the main table's query?
Will this work?

Thanks!

Are you asking if you read in the list from your first query, and then fire off an individual query for each result row? Maybe instead, you can make a list of the ID values to look for, and use where id in ( 1, 2, 3, ... ) instead of nesting? That will be a more scalable approach, making less round trips to the database.

On that note, to get the number of comments, I'd do that with your asp code rather than sending down another query along the lines of select count(*) from x where id in ( 1, 2, 3, ... ).

Thanks Forrest,

I think I understand, use something like n+1 in a while loop ..?
What will happen if a row is deleted from the articles table? For instance the record where id=4 has been deleted. !! Is there a way to still make it work?

I appreciate the help.

Chip

Hmmm, deleted rows, do you have referential integrity set in your database? If you delete ID == 4 from your articles table, the database can then automatically delete all rows with ArticleID == 4 from your comments table.

If I'm understanding your question and your system, using declarative referential integrity will prevent the situation you're worried about coding for from happening in the first place. For programmers, laziness is a virtue.

"declarative referential integrity "
Thanks, didn't know about this! very helpful..

I'm not sure I am completely understanding... Chris said that he uses 2 queries. I understand this but not really sure the best way to execute

I get confuesd soooo easily ... My concern was this:

ID-Name
01-Bob
02-Cindy
03-Stella
04-Mike
05-george

Delete a record:
ID-Name
01-Bob
02-Cindy
04-Mike
05-george

*Record three no longer exists. If I use a loop with n+1 I'm not sure how this is going to work.

Plus, I don't see how I can retrieve the number of records from table 2 without a query.

I'm thick I know... What am I not understanding?

Rereading the first message in this thread, try something like this pseudo-code:

Select
   *,
   (Select Count(*) From Comments Where ArticleID = Articles.ID) As CommmentCount
From
   Articles

The cascading deletes, or referential integrity is probably something worth looking into as well. Using a subquery, or another select statement as a column, will at least bring what you need back in a single round trip to the database.

Worked perfect Forrest. Thanks..

I learned quite a bit with this. I'm glad I made the attempt. I appreciate everyone's help.

Originally Posted by ChipJohns

Worked perfect Forrest. Thanks..

I learned quite a bit with this. I'm glad I made the attempt. I appreciate everyone's help.

All of this should be possible with one simple SQL query (Untested).

SELECT *, (SELECT count(*) FROM tbl_comments tc WHERE tc.article_id = ta.id) AS comments FROM tbl_articles ta