help with query

Using the included stored procedure I am getting the following result.
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
========================================
============
attempt at storedprocedure
========================================
============
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPost
Date
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
========================================
============
table structure being used
========================================
============
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GOI think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan