Complex SQL Help - Please

sql self join help

[Helen]

I am creating a message forum and I've been trying to figure this out for days.

I want to produce a list of all threads, sorted by the most recently active (either just posted or just replied to).

All messages are stored in the one table. Each message is stored with a msgID. Each reply also has the msgID of it's parent stored in the threadID field.

Can I do this in one query, or do I have to relate these in another table?

Here's the SQL:

select distinct a.msgID as thisthread, a.posted, ifnull(b.posted, a.posted) as replydate
from messages a left outer join messages b on a.msgID = b.threadID
where a.threadID is null
order by replydate desc;

Thanks in advance

The table:

+--------+-----------+---------------------+ 
¦ msgID ¦ threadID ¦ posted ¦ 
+--------+-----------+---------------------+ 
¦ 1 ¦ NULL ¦ 2006-01-16 13:47:09 ¦ 
¦ 2 ¦ NULL ¦ 2006-01-16 13:48:26 ¦ 
¦ 3 ¦ 1 ¦ 2006-01-16 13:56:26 ¦ 
¦ 4 ¦ 2 ¦ 2006-01-16 13:57:15 ¦ 
¦ 5 ¦ 1 ¦ 2006-01-16 13:58:26 ¦ 
+--------+-----------+---------------------+ 

[Helen]

Addition:

I can add GROUP BY to limit the number of records, but then I lose the date sort.

Thanks.

[Graham]

Helen

Not sure, but hopefully this should work. Tested it on some dummy data and it appears to do what you are after:

SELECT DISTINCT IIf(IsNull([threadID]),[msgID],[threadID]) AS ParentMessageID, Max(messages.posted) AS LatestPost
FROM messages
GROUP BY IIf(IsNull([threadID]),[msgID],[threadID])
ORDER BY Max(messages.posted) DESC;

Let me know if it is still not correct.

Graham

[mattglet]

I could be wrong, but don't you just want to show parent threads? What good would it be to show child threads? A suggestion I have is to have a column in the database that would only apply to the parent thread. In this column, you would store the "last reply" date. So whenever someone makes a reply to the thread, the "last reply" date will store in the parent's row.

You could then not have to worry about doing some complex query getting all the children, and figuring the last reply, etc.

SELECT msgID, lastreply FROM messages WHERE threadID IS NULL ORDER BY lastreply DESC

[Helen]

That did it...thank you so much

In case anyone comes looking for the same thing, I'll post the code for MySQL:

SELECT DISTINCT if(IsNull(threadID),msgID,threadID) AS ParentMessageID, CAST(Max(messages.posted) as DATETIME) AS LatestPost
FROM messages 
GROUP BY if(IsNull(threadID),msgID,threadID) 
ORDER BY LatestPost DESC;