I have two tables: "Posts" with the following Columns: ForumID, Title, Details, VisitorID, CreateDate, UpdateDate, OnStatus, IPaddress, TopicCreateName, PostViews

And "Messages" -  PostID, Title, Details, VisitorID, CreateDate, OnStatus, IPaddress, MessageCreateName

each "posts" contain messages and I would like to create an sql query that when I select all my post I will also get with each row the total number of Messages.

Any Idea ?

declare @Forums table
(
	Id int,
	Name varchar(100)
)
insert into @Forums
select 1,'a' union all
select 2,'b'

declare @Posts table
(
	Id int,
	Name varchar(100)
)
insert into @Posts
select 1,'x' union all
select 1,'y' union all
select 2,'z'

select F.Id,F.Name,count(*) from @Forums F
inner join @Posts P on F.Id = P.Id
group by F.Id,F.Name

The best way to query this kind of rich data would be to use an orm such as Linq2Sql or Linq2Entities.

That way you would get a complex object returned which contained all the posts and then the posts would contain collections of messages and you would be able to query them for more data such as

// get all messages and posts from forum 2

var query = db.Messages.Where(t => t.ForumID == 2);

// get a count of the replies to the first message in forum two

var postCount = query.First().Posts.Count();

If you are committed to using SQL then you should be searching for a JOIN tutorial.

Hiii i have used the same query in my own project which discription u posted here so not very similar but most of the part is same as you want

 SqlCommand cmd = new SqlCommand(@"SELECT     Need_Master.ID, Need_Master.UserID, CategoryMaster.Description AS CategoryName, SubCategoryMaster.Description AS SubCategoryName, 
                      Need_Master.Title, Need_Master.FileSource, Need_Master.FilePath, Need_Master.Description, Need_Master.Duration, Need_Master.Budget, 
                      Need_Master.NeedDate,
                          (SELECT     COUNT(*) AS MessageCount
                            FROM          MessageBoard
                            WHERE      (NeedID = Need_Master.ID)) AS MessageCount
FROM         Need_Master INNER JOIN
                      CategoryMaster ON Need_Master.Category = CategoryMaster.CategoryID INNER JOIN
                      SubCategoryMaster ON Need_Master.SubCategory = SubCategoryMaster.SubCategoryID
WHERE     (Need_Master.UserID = @UserID)", con);
        cmd.Parameters.AddWithValue("@UserID", UserID);

Hi,

Try this

DECLARE  @Posts TABLE
(
ForumID INT, 
Title VARCHAR(20), 
Details VARCHAR(20), 
VisitorID INT, 
CreateDate DATETIME, 
UpdateDate DATETIME, 
OnStatus BIT , 
IPaddress VARCHAR(20), 
TopicCreateName VARCHAR(20), 
PostViews INT
)


DECLARE @Messages TABLE
(
 PostID INT, Title VARCHAR(20), Details VARCHAR(20), VisitorID INT, 
 CreateDate DATETIME, OnStatus BIT, IPaddress VARCHAR(20), MessageCreateName VARCHAR(20)
)



INSERT INTO @Posts
SELECT 1,'TestTitle1','TestDetails1',1,GETDATE(),GETDATE(),1,'192.168.1.1','TopicCreateName1',10 UNION ALL
SELECT 2,'TestTitle2','TestDetails2',1,GETDATE(),GETDATE(),1,'192.168.1.1','TopicCreateName2',20 UNION ALL
SELECT 3,'TestTitle3','TestDetails3',1,GETDATE(),GETDATE(),1,'192.168.1.1','TopicCreateName3',30 
 


INSERT INTO @Messages
SELECT 1,'TestTitle1','TestDetails1',1,GETDATE(),1,'192.168.1.1','MessageCreateName1' UNION ALL
SELECT 1,'TestTitle2','TestDetails2',1,GETDATE(),1,'192.168.1.1','MessageCreateName2' UNION ALL
SELECT 2,'TestTitle3','TestDetails3',1,GETDATE(),1,'192.168.1.1','MessageCreateName3' UNION ALL
SELECT 2,'TestTitle4','TestDetails4',1,GETDATE(),1,'192.168.1.1','MessageCreateName4' UNION ALL
SELECT 3,'TestTitle5','TestDetails5',1,GETDATE(),1,'192.168.1.1','MessageCreateName5' UNION ALL
SELECT 3,'TestTitle6','TestDetails6',1,GETDATE(),1,'192.168.1.1','MessageCreateName6' UNION ALL
SELECT 3,'TestTitle7','TestDetails7',1,GETDATE(),1,'192.168.1.1','MessageCreateName7' 


SELECT ForumID, P.Title, P.Details, P.VisitorID, P.CreateDate, P.UpdateDate, P.OnStatus, P.IPaddress, 
TopicCreateName, PostViews,COUNT(*) AS NoOfMessages
FROM @Posts AS P
INNER JOIN @Messages AS M ON P.ForumID=M.PostID
GROUP BY ForumID, P.Title, P.Details, P.VisitorID, P.CreateDate, P.UpdateDate, P.OnStatus, P.IPaddress, 
TopicCreateName, PostViews

--OUTPUT

ForumID     Title                Details              VisitorID   CreateDate              UpdateDate              OnStatus IPaddress            TopicCreateName      PostViews   NoOfMessages
----------- -------------------- -------------------- ----------- ----------------------- ----------------------- -------- -------------------- -------------------- ----------- ------------
1           TestTitle1           TestDetails1         1           2009-11-07 18:00:24.077 2009-11-07 18:00:24.077 1        192.168.1.1          TopicCreateName1     10          2
2           TestTitle2           TestDetails2         1           2009-11-07 18:00:24.077 2009-11-07 18:00:24.077 1        192.168.1.1          TopicCreateName2     20          2
3           TestTitle3           TestDetails3         1           2009-11-07 18:00:24.077 2009-11-07 18:00:24.077 1        192.168.1.1          TopicCreateName3     30          3

Try this, I don't know about your relationships since you did not mention PostId in Posts table hence I matched it with ForumId

DECLARE  @Posts TABLE  
( 
ForumID INT,    
Title VARCHAR(20),    
Details VARCHAR(20),    
VisitorID INT,    
CreateDate DATETIME,    
UpdateDate DATETIME,    
OnStatus BIT ,    
IPaddress VARCHAR(20),    
TopicCreateName VARCHAR(20),    
PostViews INT  
)   
  
  
DECLARE @Messages TABLE  
(   
 PostID INT, Title VARCHAR(20), Details VARCHAR(20), VisitorID INT,    
 CreateDate DATETIME, OnStatus BIT, IPaddress VARCHAR(20), Message VARCHAR(20)   
)   
  
  
  
INSERT INTO @Posts   
SELECT 1,'TestPost1','TestDetails1',1,GETDATE(),GETDATE(),1,'192.168.1.1','Topic1',10 UNION ALL  
SELECT 2,'TestPost2','TestDetails2',1,GETDATE(),GETDATE(),1,'192.168.1.1','Topic2',20 UNION ALL  
SELECT 3,'TestPost3','TestDetails3',1,GETDATE(),GETDATE(),1,'192.168.1.1','Topic3',30    
    
  
  
INSERT INTO @Messages   
SELECT 1,'TestMessage1','TestDetails1',1,GETDATE(),1,'192.168.1.1','Message1' UNION ALL  
SELECT 1,'TestMessage2','TestDetails2',1,GETDATE(),1,'192.168.1.1','Message2' UNION ALL  
SELECT 2,'TestMessage3','TestDetails3',1,GETDATE(),1,'192.168.1.1','Message3' UNION ALL  
SELECT 2,'TestMessage4','TestDetails4',1,GETDATE(),1,'192.168.1.1','Message4' UNION ALL  
SELECT 3,'TestMessage5','TestDetails5',1,GETDATE(),1,'192.168.1.1','Message5' UNION ALL  
SELECT 3,'TestMessage6','TestDetails6',1,GETDATE(),1,'192.168.1.1','Message6' UNION ALL  
SELECT 3,'TestMessage7','TestDetails7',1,GETDATE(),1,'192.168.1.1','Message7'  

SELECT ForumID, Title, Details, VisitorID, CreateDate, UpdateDate, OnStatus, IPaddress,    
TopicCreateName, PostViews,
(
SELECT COUNT(*) FROM @Messages WHERE  ForumID=PostID
)
 MessageCount
FROM @Posts   

Output

 ForumID Title Details VisitorID CreateDate UpdateDate OnStatus IPaddress TopicCreateName PostViews MessageCount
1 TestPost1 TestDetails1 1 2009-11-07 18:22:41.957 2009-11-07 18:22:41.957 1 192.168.1.1 Topic1 10 2
2 TestPost2 TestDetails2 1 2009-11-07 18:22:41.957 2009-11-07 18:22:41.957 1 192.168.1.1 Topic2 20 2
3 TestPost3 TestDetails3 1 2009-11-07 18:22:41.957 2009-11-07 18:22:41.957 1 192.168.1.1 Topic3 30 3

Using  mudassarkhan's test tables this query would be more performant:

SELECT P.ForumID, P.Title, P.Details, P.VisitorID, P.CreateDate, P.UpdateDate, P.OnStatus, P.IPaddress,    
P.TopicCreateName, P.PostViews, M.MessagesCount

FROM @Posts P inner join (select COUNT(*) as MessagesCount, PostID from @Messages group by PostID) M on P.ForumID = M.PostID