Query help for a forum application

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 2000 Forums

SQL Server Development (2000)

Query help for a forum application

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2002-01-14 : 14:28:08

I am having a little trouble trying to figure out the best way to write a query. I have a bulletin board that I wrote a while back and I am upgrading it to include more of the features that are present in boards like UBB, vBulletin, Snitz etc.

On the forums main page I would like to list the last post and who posted it for each forum on the site. http://hondaprelude.com/webboard/

Here is the table structure for the columns that I would need to reference:

Table1 = forum_root
column1 = last_post_date
column2 = last_post_member_id
column3 = topic_id

Table2 = forum_topics
Column1 = topic_id

Table3 = members
Column1 = member_id
Column2 = nickname

I can think of a ton of ways to do this and they all seem rather slow. Basically, I need to select topic_id from forum_topics, then for each topic_id in the recordset:
select topic_id,MAX(last_post_date),last_post_member_id from forum_root Where topic_id = (the topic_id from the recordset)
But I also need to do a join on member_id in the members table to retrieve the nickname for the user. However, you can't say MAX(last_post_date), last_post_member_id, nickname from forum_root
Join on forum_root.last_post_member_id = members.member_id can you?

I know that I could select all the topic_id's and return that to ASP. Then iterate through the topic_id's and call a proc to return the last_post_date and nickname. But that does not seem efficient.

Another consideration is that I already have two queries that are something like this:

select topic_id,count(*)
from forum_root
group by topic_id,total

select topic_id,count(*)
from forum_children
group by topic_id,total

I then use these queries to determine how many threads and posts there are in each forum. I don't know whether it is faster to just leave these queries as is or when I figure out the best way to do the "last post" query to just insert all three results into a temp table and select the data from the temp table to return to ASP.

Sorry for the book. I appreciate any help you could lend.

Thanks,
Chris

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-14 : 14:37:01

graz wrote these articles:

http://www.sqlteam.com/item.asp?ItemID=1353
http://www.sqlteam.com/item.asp?ItemID=1602

HTH

TonyH
Starting Member

29 Posts

Posted - 2002-01-14 : 15:05:24

Hi,

Could this help. It is going to need some testing though.

select fr.topic_id,
fr.last_post_member_id,
m.nickname,
MAX(fr.last_post_date) 'Date Posted'
from forum_root fr inner join members m
on fr.last_post_member_id = m.member_id
group by fr.topic_id,
fr.last_post_member_id,
m.nickname
having MAX(last_post_date) = (select MAX(fr2.last_post_date)
from forum_root fr2
where fr2.topic_id = fr.topic_id)
order by topic_id, MAX(last_post_date)

TonyH
www.SQLCoder.com - Free Code generation for SQL Server 7/2000

cbrinson
Starting Member

36 Posts

Posted - 2002-01-14 : 15:55:35

Thanks for the help Tony. As soon as I posted that topic I realized that I was over complicating things. The following query actually comes close to working:

SELECT MAX(latest_post_date),topic_id
FROM forum_root
GROUP BY topic_id
ORDER BY topic_id

The problem is that I am not returning latest_post_member_id (which could then be used to get nickname from the members table.) If I add latest_post_member_id to the select list I will of course get an error saying latest_post_member_id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But if I add it to the Group By clause I get more records than I want. Any ideas? Sorry for the poorly stated original questions.

Chris

TonyH
Starting Member

29 Posts

Posted - 2002-01-15 : 03:57:35

Hi Chris,

Won't the correlated 'having' clause in the query above, only return the desired rows, even though all the rows are returned by the 'group by' clause as you said?

TonyH
www.SQLCoder.com - Free Code generation for SQL Server 7/2000

cbrinson
Starting Member

36 Posts

Posted - 2002-01-15 : 12:23:04

quote:

Hi Chris,

Won't the correlated 'having' clause in the query above, only return the desired rows, even though all the rows are returned by the 'group by' clause as you said?

Well, it is very strange. I took the query that you listed and debugged it some. However, no matter what I do it never returns the member nickname. It is like it is not even in the select. I have always wondered how you go about returning other fields in the row when using a statement like max, min etc. This has to be a common SQL problem with a fairly straightforward solution.

Given this data
Users
row_id age name home_state
1 21 Jill GA
2 34 Bob NC
3 56 Joe NC
4 20 Jim GA
5 64 Frank GA

How would you return the name and age of the oldest person from each home state? That is in a nutshell the problem that I am experiencing.
I know how to select the max age by state.
SELECT MAX(age),home_state FROM users
GROUP BY home_state

results
age home_state
56 NC
64 GA

But it seems to be a limitation of SQL that you can't select the name of the user when using a MAX function. I don't understand why that is since it is not like you are using the AVG function that would return multiple names. There is only ever going to be one name value corresponding to the MAX(age) grouped by home_state. You can't put name in the group by since that will return every pair of ages/names that has the MAX(age). The entire table.

Chris

mono
Starting Member

36 Posts

Posted - 2002-01-15 : 13:47:11

It's not a deficiency of SQL (server). You can get only one max but many records with that max.

You want something like:

select name, home_state
from Users as U1 join (SELECT MAX(age) as MaxAge,home_state FROM users
GROUP BY home_state) AS MaxAgeInState on U1.home_state = MaxAgeInState.home_state and U1.age = MaxAgeInState.MaxAge

HTH,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources