I am trying to design a blog and I want to have the typical setup where you see something like this:
Comments(some number), where the some number specifies the number of comments for a particular entry. I am thinking about two different approaches:
#1 is combining the count function into my normal query
objCmd = new OleDbCommand("SELECT TOP 10 Blog.*, BlogCategories.* FROM Blog INNER JOIN BlogCategories ON Blog.categoryID = BlogCategories.categoryID WHERE Blog.EntryDate BETWEEN '7/1/06' AND '12/31/07' ORDER BY Blog.EntryDate DESC", objConn);
I would need to add an additional inner join to get to the table, BlogComments, to be able to count the correct number of comments for a specific entry, and obviously incorporate the Count function into my query as well.
Option #2 is creating a new column in my Blog datatable and just keep track of the number of comments by adding or subtracting (++ or --) from a record when a new comment is added or deleted. (I think this is easier but can have obvious problems if non standard changes are made to the blog).
Your best bet is like your 1st option, but instead of a join you use a subquery in your select list.
Add something like the following as your third column in your select list, and wrap parentheses around it
SELECT COUNT(*) FROM BlogComments WHERE BlogID = Blog.BlodID
|||You can also send two separate statements in your command ("select top 10, etc......order by desc; select count(*)....", objConn)
and then you'll have two tables in your dataset or you can use .NextResult() on the Reader object.
|||I tried your suggestion and I get an error: The multi-part identifier "Blog.BlogID" could not be bound.
Do I need an alias? Do I need to declare the value?
objCmd = new OleDbCommand("SELECT TOP 10 Blog.*, BlogCategories.* FROM Blog INNER JOIN BlogCategories ON Blog.categoryID = BlogCategories.categoryID WHERE Blog.EntryDate BETWEEN '7/1/06' AND '12/31/07' ORDER BY Blog.EntryDate DESC; SELECT COUNT(*) AS NumComments FROM BlogComments WHERE BlogCID = Blog.BlogID", objConn);
|||It was a silly assumption that there would be something as useful as a unique way to identify each Blog thread.
Please post your table DDL and sample data in the form of INSERT statements.
|||SELECT TOP 10 Blog.*, BlogCategories.*, (select count(*) from BlogComments where BlogComments.<BlogKey> = Blog.<BlogKey>) as CommentCount
FROM Blog
INNER JOIN BlogCategories
ON Blog.categoryID = BlogCategories.categoryID
WHERE Blog.EntryDate BETWEEN '7/1/06' AND '12/31/07'
ORDER BY Blog.EntryDate DESC
Now, you need to fill in the BlogKey values, whatever they are (and there could be > 1 columns in the key.) If this doesn't make sense then you really do need to post your structures...
|||You are an optimist Louis. <BlogKey> is as likely to be comprehended as [BlogComments] or [BlogID]|||Each query in the command string needs to be able to execute on its own.
Apparently Blog.BlogID isn't in the table you're referencing.
|||Here are my tables and the columns within them:
Blog: BlogID(smallint), BlogEntry(varchar),State(varchar),Body(ntext), FirstName(varchar), LastName(varchar), CategoryID(varchar), Email(varchar), EntryDate(datetime)
BlogCategories: CategoryName(nchar), CategoryID(varchar)
BlogComments: BlogCID(smallint), Comment(ntext), FirstName2 (varchar), LastName2(varchar), Email2(varchar), CommentDate(datetime)
FYI: In my original post I had listed BlogID in both the BlogComments and Blog tables. I later changed the BlogComments' BlogID to BlogCID to ensure there was no conflict.
As far as how my pages are setup, I have a page that displays an entry, all comments connected to it, and additional comments can be entered. I have another page where new entries can be added. Then the third page, the main page, where I want to have a listing of entries and the number of comments for each attached to it is the one that is not working.
|||Thanks, that is very helpful.
Now an additional question.
What links a Blog with its Comments?
(It seems like BlogComments 'should' have the BlogID as a Foriegn Key.)
|||both BlogID and BlogCID are primary keys and this is the information that connects the entry and the comments. On my other page where I display the comments along with the corresponding entry I have two separate functions, the connecting factor is that I do a URL query to collect a variable designating the entry and then both the entry and subsequent comments are each independently called based on that variable. So, joining the tables has been a problem I have been able to avoid until now.|||So now I'm confused here.
If BlogCID is a Primary Key for the BlogComments table, and if BlogID is a Primary Key for the Blogs table, AND they are used to link the two tables, that would mean there could only be ONE comment per blog. That doesn't seem quite right.
|||Sorry, you are correct, there is no primary key for the Comments table.|||Thanks for the clarification.
You would 'best' position the BlogComments table if you were to add a Primary key, perhaps an IDENTITY field.
And name it BlogCommentsID. (A good naming practice is {TableName}ID for IDENTITY fields.)
Without a Primary Key (or other method) to specifically identify each individual row, the row would NOT be editable.
I would rename the existing BlogCID to BlogID -that makes it easier to 'see' that there is a PK-FK relationship with the Blogs table.
Then this revision of your previous query 'should' work as you want. (NOTE: I have assumed renaming the BlogCID to BlogID.)
SELECT TOP
10 b.*,
bc.*,
bcom.ThreadCnt
FROM Blog b
INNER JOIN BlogCategories bc
ON b.categoryID = bc.categoryID
JOIN (SELECT
BlogID,
ThreadCnt = count(1)
FROM blogComments
GROUP BY BlogID
) bcom
ON b.BlogID = bcom.BlogID
WHERE b.EntryDate BETWEEN '7/1/06' AND '12/31/07'
ORDER BY b.EntryDate DESC;
No comments:
Post a Comment