quote:

---

Originally posted by rockmoose

By joining the 2 tables and using a GROUP BY You will be able to do this.
this shows the concept:
select a,b,c,max(x),sum(y)
from t1 join t2 on t1.key = t2.fkey
group by a,b,c

---

declare @forum Table
	(forum_au int identity
	,forum_name varchar(50))

declare @thread Table
	(forum_au int
	,thread_au int identity
	,thread_title varchar(50)
	,thread_lastpost_date datetime
	,thread_lastpost_author varchar(50)
	,thread_lastpost_id int
	,thread_viewcount int)

Select	f.forum_au
	,f.forum_name
	,t.thread_lastpost_id
	,t.thread_lastpost_author
	,t.thread_lastpost_date
	,t.sumViewCount
From	@forum f
--Left join to get all forums with or without threads

Left JOIN (--get all thread data for most recent post
	Select	t1.forum_au
		,t1.thread_lastpost_id
		,t1.thread_lastpost_author
		,t1.thread_lastpost_date
		,agg.sumViewCount
	From	@thread t1

	JOIN	(--get the most recent post 
		Select	forum_au
			,max(thread_lastpost_date) maxDate
			,count(*) sumViewCount
		From	@thread
		group by forum_au
		) as agg ON t1.forum_au = agg.forum_au
			AND t1.thread_lastpost_date = maxDate

	) as t
		ON f.forum_au = t.forum_au

SELECT
	f.forum_au
	,f.forum_name
	,thread_lastpost_id = 	(	SELECT t.thread_lastpost_id
					FROM thread t
					WHERE t.forum_au = f.forum_au
					AND t.thread_lastpost_date = last_post.last_date )
	,thread_lastpost_author = (	SELECT t.thread_lastpost_author
					FROM thread t
					WHERE t.forum_au = f.forum_au
					AND t.thread_lastpost_date = last_post.last_date )
	,sum_viewcount =	(	SELECT SUM(t.thread_viewcount)
					FROM thread t
					WHERE t.forum_au = f.forum_au )
FROM
	forum f
	LEFT JOIN(	SELECT forum_au, MAX(thread_lastpost_date) AS last_date
			FROM thread
			GROUP BY forum_au ) last_post
		ON f.forum_au = last_post.forum_au