SQL Help - SQL Server Forums

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)

SQL Help

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-04-06 : 16:49:21

I'm working on a basic forum and I am trying to show the "Topic Starter"

This query is working nicely, but without the AuthorID of the topic starter.

SELECT Subject, NoOfViews, TopicID, B.Posts
FROM OI_Topic A
LEFT OUTER JOIN
(
SELECT Count(TopicID) As Posts, TopicID
FROM OI_Thread
GROUP BY TopicID
) AS B
ON A.TopicID = B.TopicID
WHERE ForumID = '1'

Now, I am trying to get the 1st Date of the post, this will be the topic starter. However, when I do a SELECT TOP 1, it only selects the AuthorID for one topic, which makes sense. Is it possible to show the topic starter? If you go to http://rockenbach.net/topic.gif
You can see the query in rows in EM.

SELECT Subject, NoOfViews, TopicID, B.Posts, C.AuthorID
FROM OI_Topic A
LEFT OUTER JOIN
(
SELECT Count(TopicID) As Posts, TopicID
FROM OI_Thread
GROUP BY TopicID
) AS B
ON A.TopicID = B.TopicID
LEFT OUTER JOIN
(
SELECT TOP 1 AuthorID, TopicID
FROM OI_Thread
ORDER BY messagedate ASC
) AS C
ON A.TopicID = C.TopicID
WHERE ForumID = '1'

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 17:13:14

Try changing this query:

SELECT TOP 1 AuthorID, TopicID
FROM OI_Thread
ORDER BY messagedate ASC

into

SELECT TopicID, AuthorID
FROM OI_Thread A
WHERE messagedate = (SELECT MIN(messagedate) FROM OI_Thread B
WHERE A.TopicID = B.TopicID)

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-04-06 : 18:22:22

WOW! Perfect! That is just what I needed. Thank you, I learned something new.

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-04-09 : 19:37:23

I need help again, this is almost the same deal as before. This the first page of the forum and I am trying to show the last post. There are 3 forums, so there should only be 3 rows returned (by the most recent date.

Here is my SQL. If you go to http://rockenbach.net/topic.gif you can see a screen print of EM

SELECT A.ForumID, A.ForumName, A.ForumDescription, A.DateStarted, A.NoOfTopics, A.NoOfPosts, C.MessageDate
FROM OI_Forum A
LEFT OUTER JOIN
(
SELECT TopicID, ForumID, LastEntryDate
FROM OI_Topic A
WHERE LastEntryDate = (SELECT MAX(LastEntryDate) FROM OI_Topic B
WHERE A.TopicID = B.TopicID)
) AS B
ON A.ForumID = B.ForumID
LEFT OUTER JOIN
(
SELECT UserID, TopicID, MessageDate
FROM OI_Thread A
WHERE MessageDate = (SELECT MIN(messagedate) FROM OI_Thread B
WHERE A.TopicID = B.TopicID)
)
AS C
ON B.TopicID = C.TopicID

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-04-10 : 08:45:33

I think I have figured it on my own. After breaking it down into smaller chunks I see the problem. The one you originally helped with works perfectly. I was trying to use your technique to a similiar page ( the main forum page) This is working for me so far.

SELECT A.ForumID, A.ForumName, A.ForumDescription, A.NoOfTopics, A.NoOfPosts, B.LastEntryDate
FROM OI_Forum A
LEFT OUTER JOIN
(
SELECT TopicID, ForumID, LastEntryDate
FROM OI_Topic A
WHERE LastEntryDate = (SELECT MAX(LastEntryDate) FROM OI_Topic B
WHERE A.ForumID = B.ForumID)
) AS B
ON A.ForumID = B.ForumID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources