difficult SQL join query - any ideas? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

difficult SQL join query - any ideas?

Author

Topic

sengoku
Starting Member

29 Posts

Posted - 2003-07-11 : 06:24:15

hey there peeps

i have 2 tables for a forum application, 1 of them contains the forum threads, and 1 contains the messages. obviously the messages have message_thread which points to the thread id, and the threads table has a couple of columns : lastpost_id, lastpost_datetime to avoid heavily hammering the messages table.

here's the columns from the tables :

data_threads

thread_au int
thread_title varchar
thread_lastpostdt datetime
thread_lastpostid int

data_messages

message_au int
message_thread int
message_man int
message_date datetime
message_text varchar

and i can easily query the last 10 messages someone's posted with :
SELECT TOP 10 m.message_au,m.message_date,t.thread_title,t.thread_au,t.thread_lastpostid from data_forummessages m,data_forumthreads t where m.message_thread=t.thread_au and m.message_man=@thisUser order by m.message_date desc

but what i want to do is this :
get a list of the top 10 *distinct* threads you've posted in, along with the latest message id and date you posted. there any clever way to do this all in 1 query? or do i need to build a temp table, or just loop through the threads table in the front end and run a 'select top 1 from data_messages' query for each of the threads?

any help greatfully appreciated :)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-11 : 06:58:59

SELECT TOP 10 m.message_au, m.message_date, t.thread_title,
t.thread_au, t.lpdt, t.lpid from data_forummessages m,

(select thread_au, thread_title, max(thread_lastpostdt) lpdt,
max(thread_lastpostid) lpid from data_forumthreads
group by thread_au, title_au) t

where
m.message_thread=t.thread_au and m.message_man=@thisUser
order by m.message_date desc

- Vit

sengoku
Starting Member

29 Posts

Posted - 2003-07-11 : 07:10:36

hmm, actually that's the wrong way round... i only want to return 1 row for each thread, with the last message id and date posted by a certain manager in that thread... is that possible?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-11 : 07:20:43

... or rather this (a bit difficult to understand what's going on):

SELECT TOP 10
m.message_au, t.thread_title, t.thread_au,
max(t.thread_lastpostid) lpid, max(m.message_date) lmdt
from data_forummessages m, data_forumthreads t
where
m.message_thread=t.thread_au and m.message_man=@thisUser
group by m.message_au, t.thread_title, t.thread_au

- Vit

sengoku
Starting Member

29 Posts

Posted - 2003-07-11 : 07:31:03

hmm... ok, step by step what i want to do is this :

1) query a list of the last 10 threads that a specific manager has posted in. each row is a distinct thread, no matter if the person has posted multiple times in that thread - SELECT DISTINCT TOP 10 message_thread, MAX(message_date) AS lpdt FROM data_messages WHERE (message_man = @thisUser) GROUP BY message_thread ORDER BY lpdt DESC
2) for each of those threads, return the thread details (including the last post id and datetime) along with the id# and date of the latest message by that person - select top 1 message_au,message_date from data_messages where message_man=@thisUser and message_thread=thisThread

and i want to order the whole lot by the latest MESSAGE posted by said person, as opposed to the thread's last post

it looks to me like i might need to use a temp table but i'm not a sql guru and there might well be a clever way of doing it...

Edited by - sengoku on 07/11/2003 07:33:41

Edited by - sengoku on 07/11/2003 08:01:15

sengoku
Starting Member

29 Posts

Posted - 2003-07-11 : 08:09:13

ooh, i fixed it myself! :)

SELECT t.*, m.lpdt AS myLastPostDT, n.message_au AS myLastPostID
FROM data_forumthreads t,(SELECT DISTINCT TOP 10 message_thread, MAX(message_date) AS lpdt FROM data_forummessages WHERE message_man = @thisUser GROUP BY message_thread ORDER BY lpdt DESC) m, data_forummessages n WHERE t.thread_au = m.message_thread AND m.lpdt = n.message_date and n.message_man = @thisUser

i had to do the join with the messages table just incase the id numbers weren't squential (otherwise i could have just added MAX(message_au) into the nested query)

but thanks for the assistance stoad, you gave me the right way to look at it :)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-11 : 08:20:28

sengoku,

I am very glad to hear this good news. :)

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources