How do you display comment count if there is no comment posted it will display in a formview?

Question

I am building a blog for school. i would like to display the count of comments for each thread made. However I'm a bit lost to how to achieve this goal. any help would be great thank you!

i have 2 tables

CREATE TABLE `blog_message` (
  `MessageID` int(30) NOT NULL AUTO_INCREMENT,
  `Username` varchar(45) NOT NULL,
  `Message` text,
  `AddedDate` datetime DEFAULT NULL,
  `Title` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`MessageID`)
)


CREATE TABLE `blog_comments` (
  `CommentID` int(30) NOT NULL AUTO_INCREMENT,
  `MessageID` int(30) DEFAULT NULL,
  `Author` varchar(45) DEFAULT NULL,
  `CommentMessage` text,
  `AddedDate` datetime DEFAULT NULL,
  PRIMARY KEY (`CommentID`),
  KEY `blog_comments_ibfk_1` (`MessageID`),
  CONSTRAINT `blog_comments_ibfk_1` FOREIGN KEY (`MessageID`) REFERENCES `blog_message` (`MessageID`)
)

My formview code

<asp:FormView ID="FormView1" runat="server" AllowPaging="True" DataSourceID="SqlDataSource1">      
                       <ItemTemplate>    
                           <div>
                           <asp:Label ID="lblcc" runat="server" Text="Comment Replys"></asp:Label>
                           &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                           <asp:Label ID="lblT" runat="server" Text="Title"></asp:Label>
                           &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                           &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                           <asp:Label ID="lblA" runat="server" Text="Author"></asp:Label>
                           &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                           &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                           <asp:Label ID="lbldoc" runat="server" Text="Date of Creation"></asp:Label>
                           </div>                                     
                            <hr />      
                            <asp:LinkButton ID="LinkButton1" runat="server" Font-Underline="false" PostBackUrl='<%# Eval("MessageID", "BlogComments.aspx?MessageID={0}") %>' >                         
                            <asp:Label ID="lblCommentCounts" runat="server" Text='<%# Eval("comment_count") %>'></asp:Label>
                            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;                   
                            <asp:Label ID="lblTitle" runat="server" Text='<%# Eval("Title") %>'></asp:Label>
                            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            <asp:Label ID="lblAuthor" runat="server" Text='<%# Eval("Author") %>'></asp:Label>
                            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            <asp:Label ID="lbDateOfCreation" runat="server" Text='<%# Eval("AddedDate") %>'></asp:Label>       
                            </asp:LinkButton>  
                            <hr />
                        </ItemTemplate>
                    </asp:FormView>

MySQL code

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
        SelectCommand="SELECT bm.MessageId, count(bc.CommentId) as comment_count, bm.AddedDate, bm.Author, bm.Title
FROM blog_comments bc, blog_message bm
WHERE bm.MessageId = bc.MessageId 
GROUP BY bm.MessageId"></asp:SqlDataSource>

my goal is

to display in a gridview

table format:

Comment count | title | Username | Date of creation

However it would only display if the thread has comments. how do i display 0 comments if the thread has 0 comments?

Answer 1

Use an outer join instead of an inner join:

SELECT
    bm.MessageId,
    COUNT(bc.CommentId) AS comment_count,
    bm.AddedDate,
    bm.Author,
    bm.Title
FROM blog_message bm
LEFT JOIN blog_comments bc
ON bm.MessageId = bc.MessageId 
GROUP BY bm.MessageId