value of lastest post and username

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

value of lastest post and username

Author

Topic

jamest85
Starting Member

6 Posts

Posted - 2007-12-13 : 16:35:48

Hi: I have 3 tables (Categories, Topics, Posts)
The output will be:
CategoriesName;#ofTopics;#ofPosts;LastestPostDate;UserName
Sports;3;34;12/10/2007;jamest85

So far I can get first 3 coloums, but I just don't know how to get the latestPostDate and username value from Posts Table.

Here is the code for getting 3 coloums:

SELECT tblForumsCategories.CategoriesName AS Categories, COUNT(DISTINCT tblForumsTopics.TopicsName) AS #Topics, COUNT(DISTINCT tblForumsDetails.DetailsText) AS #Posts
FROM tblForumsCategories INNER JOIN tblForumsTopics ON tblForumsCategories.CategoriesID = tblForumsTopics.CategoriesID LEFT OUTER JOIN tblForumsDetails ON tblForumsCategories.CategoriesID = tblForumsDetails.CategoriesID AND tblForumsTopics.TopicsID = tblForumsDetails.TopicsID
GROUP BY tblForumsCategories.CategoriesName

Those data will be put in a gridview (asp.net2.0)
Do I need to run seperate sql to get the lastestDate and username?

Thank you very much

Jamest85

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-12-13 : 21:47:52

Where is the UserID / UserName / PostDate stored?

Also, check out this article: http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx

Nathan Skerl

jamest85
Starting Member

6 Posts

Posted - 2007-12-13 : 22:31:17

Hi: Nathan

Thanks for your post, those data are all in table: Posts, in that table also has: CategoriesID, TopicsID, so other two tables (Categories, Topics) can link with it.

I am reading the artical you provide now. Hope it helps, but you have any suggestion, please also help out.

Thanks

jamest85

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-12-13 : 22:46:53

Im having trouble following your data model, it would help us a lot if you gave us some sample data to work with. You can use MAX funtions to get at the datetime/user of the latest post, but if youre open to some input on redesigning the data model the sample data would help.

Use this as a starting point:


declare @tblForumsCategories table (CategoriesID int, CategoriesName varchar(100))
declare @tblForumsTopics table (TopicsID int, CategoriesID int, TopicsName varchar(100))
declare @tblForumsDetails table (CategoriesID int, TopicsID int)
declare @tblForumsPosts table (PostID int, TopicsID int, UserID int, PostText varchar(100), PostTime datetime)
declare @tblForumUsers table (UserID int, UserName varchar(100))


insert into @tblForumUsers
	select 1, 'James' union
	select 2, 'Nathan'
insert into @tblForumsCategories
	select 1, 'Sports'
insert into @tblForumsTopics
	select 1, 1, '?????' union
	select 2, 1, '?????'
insert into @tblForumsDetails
	select 1, 1
insert into @tblForumsPosts
	select 1, 1, 1, '??????', '2007-12-10' union
	select 1, 1, 2, '??????', '2007-12-11'

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-12-13 : 23:09:23

using my example, you can see how you can approach:


declare @tblForumsCategories table (CategoriesID int, CategoriesName varchar(100))
declare @tblForumsTopics table (TopicsID int, CategoriesID int, TopicsName varchar(100))
declare @tblForumsDetails table (CategoriesID int, TopicsID int)
declare @tblForumsPosts table (PostID int, TopicsID int, UserID int, PostText varchar(100), PostTime datetime)
declare @tblForumUsers table (UserID int, UserName varchar(100))


insert into @tblForumUsers
	select 1, 'James' union
	select 2, 'Nathan'
insert into @tblForumsCategories
	select 1, 'Sports' union
	select 2, 'Arts'
insert into @tblForumsTopics
	select 1, 1, '?????' union
	select 2, 1, '?????'
insert into @tblForumsDetails
	select 1, 1
insert into @tblForumsPosts
	select 1, 1, 1, '??????', '2007-12-10' union
	select 1, 1, 2, '??????', '2007-12-11'


select	p.PostID,
	p.UserID,
	p.PostTime
from	@tblForumsPosts p
join	@tblForumsTopics t on
	p.TopicsID = t.TopicsID
join	(	select	c.CategoriesID,
			max(p.PostTime) [LastPost]
		from	@tblForumsCategories c
		join	@tblForumsTopics t on
			c.CategoriesID = t.CategoriesID
		join	@tblForumsPosts p on
			t.TopicsID = p.TopicsID
		group
		by	c.CategoriesID
	) d on
	t.CategoriesID = d.CategoriesID and
	p.PostTime = d.LastPost

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources