Basic query a problem (hurts my head -.- ) 1

[Silx]

Greetings!

I am new here, but this looks to be a fine place to get an answer to my noob-like question.

My query involves two tables. FORUM_TOPIC and FORUM_REPLY.

What I'm trying to do is get only the latest FORUM_REPLY.FORUM_REPLY_DATE, for each FORUM_TOPIC. The two tables are linked via FORUM_TOPIC.FORUM_TOPIC_ID.

Ideally, I'd like the date to return null if there is no reply at all. And the latest date if there is more than one reply. Bottom line, there can only be one record returned for each unique topic id.

Thanks for your time!

Here is my retarded query as it stands now:

select Forum_topic.Account_id, Forum_Reply.Account_ID, forum_reply.Forum_Reply_Date, Forum_topic.Forum_Topic_ID, Forum_Topic_Name 

from Forum_Topic 

left outer join forum_reply 

on forum_reply.forum_topic_id=forum_topic.Forum_Topic_ID 

where forum_topic.forum_category_id=927

 

[Silx]

I'm back again!

Seems that the topic id is getting away. It needs to always be returned. But it seems to not be returned when there are no replies. Here is the current query...

select Tbl2.Forum_Topic_ID,
       Forum_Topic_Name,
       Forum_Topic_Replies,
       Forum_Topic_LastPost,
       Account.Account_Username as Account_Username,
       Forum_Topic_Locked,
       Forum_Topic_Sticky,
       Forum_topic.Account_id as Poster_Account_ID,
       Tbl2.Account_ID as Last_Reply_Account_ID,
       Tbl2.Forum_Reply_Date,
       Tbl2.Username as Last_Reply_Account_Username
from Forum_Topic
left join (SELECT Forum_Reply.forum_topic_id,
                  Forum_Reply.Account_ID,
                  forum_reply.Forum_Reply_Date,
                  Account.ACCOUNT_USERNAME AS UserName
           FROM forum_reply
           INNER JOIN (SELECT forum_topic_id,
                              MAX(Forum_Reply_Date) AS Forum_Reply_Date
                       FROM forum_reply
                       GROUP BY forum_topic_id) Tbl1
           ON forum_reply.forum_topic_id   = Tbl1.forum_topic_id AND
              forum_reply.Forum_Reply_Date = Tbl1.Forum_Reply_Date
           LEFT JOIN Account ON Forum_Reply.Account_ID = Account.Account_ID) Tbl2
on Forum_Topic.forum_topic_id=Tbl2.Forum_Topic_ID
left join Account on Forum_Topic.Account_ID=Account.Account_ID
where forum_topic.forum_category_id=927

Hopefully this is the last time for this damn query. -.-

Thanks!

 

[Silx]

Woo! I think I got it.

I just changed Tbl2.Forum_Topic_ID to Forum_Topic.Forum_Topic_ID.

Seemed to have done the trick.

See? I try!
lol

thanks again guys.

 

[Silx]

Hi guys! I'm back.. for more help. :<

The code above is working great, however.. I'm trying to modify it to work on an 'active topics' page, which consists of a lot more data.

Let me explain it:

There are 5 tables we're working with here..
FORUM
FORUM_CATEGORY
FORUM_TOPIC
FORUM_REPLY
ACCOUNT

The tables are linked as follows..
FORUM_REPLY.FORUM_TOPIC_ID = FORUM_TOPIC.FORUM_TOPIC_ID
FORUM_TOPIC.FORUM_CATEGORY_ID = FORUM_CATEGORY.FORUM_CATEGORY_ID
FORUM.FORUM_ID=FORUM_CATEGORY.FORUM_ID

We'll need information from 2 accounts (like in the code already figured out above).. one for the first poster, one for the last poster.
1) FORUM_TOPIC.ACCOUNT_ID, FORUM_TOPIC.FORUM_TOPIC_DATE, ACCOUNT.ACCOUNT_USERNAME
2) FORUM_REPLY.ACCOUNT_ID, FORUM_TOPIC.FORUM_TOPIC_LASTPOST, ACCOUNT.ACCOUNT_USERNAME

And then of course the information for each active topic, sorted by date..

FORUM_TOPIC.FORUM_TOPIC_ID
FORUM_TOPIC.FORUM_TOPIC_NAME
FORUM_TOPIC.FORUM_TOPIC_REPLIES
FORUM_TOPIC.FORUM_TOPIC_LASTPOST
FORUM_TOPIC.FORUM_TOPIC_LOCKED
FORUM_TOPIC.FORUM_TOPIC_STICKY
FORUM_TOPIC.FORUM_TOPIC_DATE
FORUM_CATEGORY.FORUM_CATEGORY_NAME
FORUM_CATEGORY.FORUM_CATEGORY_ID
ACCOUNT.ACCOUNT_USERNAME (x2)
ACCOUNT.ACCOUNT_ID (x2)

Here is the current active topics code. It works, but does not have all the extra information I need as described above:

select Forum_Topic_ID,
       Forum_Topic_Name,
       Forum_Topic_Replies,
       Forum_Topic_Lastpost,
       Account_Username,
       Forum_Topic_Locked,
       Forum_Topic_Sticky,
       Forum_Category_Name,
       Forum_Topic.Forum_Category_ID
from dbo.Forum, dbo.Forum_Topic, dbo.Account, dbo.Forum_Category
Where Forum.Forum_ID=@ForumID_input and Forum_Category.Forum_ID=Forum.Forum_ID and Account.Account_ID=Forum_Topic.Account_ID and Forum_Category.Forum_Category_ID=Forum_Topic.Forum_Category_ID
Order By Forum_Topic_Lastpost Desc

I hope one of you sql gurus can help me out here. I'm still trying to grasp a lot of the sql syntaxes for joins and whatnot. ><

thanks a mil.