We will end this article by creating a query to report on
forum users. For this report, we will create something that allows us to view
how many posts someone has and drill down to the forums this would be on.
To create that query, we will need to use the following
tables: Forum_Posts, Forum_Threads and Forum_Forums. To discover which users,
we will need to add the Users table.
Listing 1
select fp.[Subject]
, substring(fp.[Body],1,4000) as 'Body'
, ff.[Name] as 'Forum'
, (select count(*) from dbo.Forum_Threads ft2
where ft2.threadid = fp.threadid) as 'TotalThreads'
, ft.replies
, us.Username
, us.FirstName
, us.LastName
from dbo.Forum_Posts fp
inner join dbo.Forum_Threads ft on (ft.threadid = fp.ThreadID)
inner join dbo.Forum_Forums ff on (ff.ForumID = ft.ForumID)
inner join dbo.Users us on (us.UserID = fp.UserID)
inner join dbo.UserRoles ur on (us.UserID = ur.UserID)
inner join dbo.aspnet_Roles aspr on (ur.RoleId = aspr.RoleId)
This query should show us information on the forums, such as
how many replies there are for each post. Also, we include the basic
information on the person who posted. Things like email and even the role of
the user can be included. The last part would need another table added to your
code. See listing 2 for an example of how this would look.
Listing 2
select fp.[Subject]
, substring(fp.[Body],1,4000) as 'Body'
, ff.[Name] as 'Forum'
, (select count(*) from dbo.Forum_Threads ft2
where ft2.threadid = fp.threadid) as 'TotalThreads'
, ft.replies
, us.Username
, us.FirstName
, us.LastName
, aspr.RoleName
from dbo.Forum_Posts fp
inner join dbo.Forum_Threads ft on (ft.threadid = fp.ThreadID)
inner join dbo.Forum_Forums ff on (ff.ForumID = ft.ForumID)
inner join dbo.Users us on (us.UserID = fp.UserID)
inner join dbo.UserRoles ur on (us.UserID = ur.UserID)
inner join dbo.aspnet_Roles aspr on (ur.RoleId = aspr.RoleId)
In the next part of this series we will take these queries
and put them into a Crystal report.
