Hi baijuep,
Please refer below SQL Query and apply according to your requirement
CREATE TABLE #BlogPost (ID INT IDENTITY, Title VARCHAR(MAX),Content VARCHAR(MAX),Author VARCHAR(100),BlogDate DATETIME)
INSERT INTO #BlogPost VALUES('Biju''s First blog','Test','Biju EP','2016/11/11')
INSERT INTO #BlogPost VALUES('Simulator','Test lines','Maj RK Narban','2016/11/11')
INSERT INTO #BlogPost VALUES('Lethal Drones','This page is rese.....','Maj DS Misra','2016/11/11')
CREATE TABLE #CommentTable(ID INT IDENTITY,Comment VARCHAR(MAX),Name VARCHAR(100),[Date] DATETIME,BlogId INT)
INSERT INTO #CommentTable VALUES('Good line','Sanath','2016/11/11',1)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',3)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',4)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',3)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',3)
INSERT INTO #CommentTable VALUES('This is a good blog','Sanu Kumar','2016/11/12',3)
INSERT INTO #CommentTable VALUES('You need not have....','Baiju','2016/11/15',3)
SELECT B.ID,Title,Content,Author,BlogDate,ISNULL((SELECT COUNT(BlogId) FROM #CommentTable WHERE BlogId = B.ID GROUP BY BlogId),'0')Replies
FROM #BlogPost B
DROP TABLE #BlogPost
DROP TABLE #CommentTable
OutPut
ID |
Title |
Content |
Author |
BlogDate |
Replies |
1 |
Biju's First blog |
Test |
Biju EP |
11/11/16 0:00 |
1 |
2 |
Simulator |
Test lines |
Maj RK Narban |
11/11/16 0:00 |
0 |
3 |
Lethal Drones |
This page is rese..... |
Maj DS Misra |
11/11/16 0:00 |
5 |