CODEWITHSUNDEEP
mysqlsqlsql-serverinner-joinquery-builder
WeakTaenie
WeakTaenie

Reputation: 247

SQL-Unable to display count value that contain 0

There is 2 Table Discussion and Comment, Discussion tablehas D1 and D2 Comment table has (C1,D2),(C2, D2) 

SELECT        Discussion.DiscussionID, COUNT(Comment.CommentID) AS Expr1
FROM            Comment INNER JOIN
                     Discussion ON Comment.DiscussionID = Discussion.DiscussionID INNER JOIN
                     Category ON Discussion.CategoryID = Category.CategoryID
GROUP BY Discussion.DiscussionID

I want a output like this ...

Dicussion ID||   Comment COunt
D1                  0
D2                  2

but the the result like this

Dicussion ID||   Comment COunt
D2                  2

Upvotes: 1

Views: 33

Answers (2)

SoulTrain
SoulTrain

Reputation: 1904

Or use a Left Join

SELECT Discussion.DiscussionID,
       Count(Comment.CommentID) AS Expr1
FROM   Discussion
       INNER JOIN Category
               ON Discussion.CategoryID = Category.CategoryID
       LEFT JOIN Comment
               ON Comment.DiscussionID = Discussion.DiscussionID
GROUP  BY Discussion.DiscussionID

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

convert the Inner Join to Right Join so will get all the rows from Discussion table.

SELECT Discussion.DiscussionID,
       Count(Comment.CommentID) AS Expr1
FROM   Comment
       RIGHT JOIN Discussion
               ON Comment.DiscussionID = Discussion.DiscussionID
       INNER JOIN Category
               ON Discussion.CategoryID = Category.CategoryID
GROUP  BY Discussion.DiscussionID

Upvotes: 4

Related Questions