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

 

[bborissov]

[COLOR=blue]select[/color] Forum_topic.Account_id,
       Tbl2.Account_ID,
       Tbl2.Forum_Reply_Date,
       Tbl2.Forum_Topic_ID,
       Forum_Topic_Name
[COLOR=blue]from[/color] Forum_Topic
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] ([COLOR=blue]SELECT[/color] Forum_topic.forum_topic_id,
                  Forum_topic.Account_id,
                  Forum_Reply.Account_ID,
                  forum_reply.Forum_Reply_Date
           [COLOR=blue]FROM[/color] forum_reply
           [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] forum_topic_id,
                              [COLOR=#FF00FF]MAX[/color](Forum_Reply_Date) [COLOR=blue]AS[/color] Forum_Reply_Date
                       [COLOR=blue]FROM[/color] forum_reply
                       [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] forum_topic_id) Tbl1
           [COLOR=blue]ON[/color] forum_reply.forum_topic_id   = Tbl1.forum_topic_id AND
              forum_reply.Forum_Reply_Date = Tbl1.Forum_Reply_Date) Tbl2
[COLOR=blue]on[/color] forum_reply.forum_topic_id=Tbl2.Forum_Topic_ID
[COLOR=blue]where[/color] forum_topic.forum_category_id=927

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[maswien]

try this:

select Forum_topic.Forum_Topic_Name,
max(forum_reply.Forum_Reply_Date)
from Forum_Topic
left outer join forum_reply
on forum_reply.forum_topic_id=forum_topic.Forum_Topic_ID
group by Forum_topic.Forum_Topic_Name

 

[Silx]

Alright, thank you for the quick responses.

bborissov, I get these errors when running your query:

The column prefix 'Forum_topic' does not match with a table name or alias name used in the query.
The column prefix 'Forum_topic' does not match with a table name or alias name used in the query.
The column prefix 'forum_reply' does not match with a table name or alias name used in the query.
(0.594 sec)

maswien, yours works very well. However, I do need the remaining data (such as forum_reply.account_id) without getting those nasty repeats back again. Any ideas?

once again, thank you for the help!

 

[koooob]

If you are on sql server, you could create a function that gives you the id of the post. Not sure about performance but it will work (I've used this method for things before)

so in query analyser create this function:

CREATE FUNCTION dbo.last_forum_post_id (
	@topic_id uniqueidentifier
)
RETURNS uniqueidentifier AS  
begin
	return (select top 1 forum_topic_id from forum_reply where forum_topic_id=@topic_id order by Forum_Reply_Date desc)
end

then you could say:

SELECT     forum_topic_id, dbo.last_forum_post_id(forum_topic_id) AS last_reply_id
FROM         Forum_topic

which will give you a list of topic IDs and the last post ID. (if any) you could then either save this as a view and link that way or just link directly on this field:

SELECT     Forum_topic.forum_topic_name, dbo.last_forum_post_id(Forum_topic.forum_topic_id) AS last_reply_id, forum_reply.account_id, 
                      forum_reply.forum_reply_date
FROM         Forum_topic LEFT OUTER JOIN
                      forum_reply ON forum_reply.forum_topic_id = dbo.last_forum_post_id(Forum_topic.forum_topic_id)

Should do the job!

 

[maswien]

silx,

Did you try my SQL? This should be typical left outer join query and answer should be pretty straight forward

 

[Silx]

I did maswien, and as I said already above (looks like you missed it perhaps), I need the remaining data in my query, such as the forum_reply.account_id. If I add those, I start getting multiple records for one topic.

Your code works! I just need that extra info

 

[maswien]

The reason you got multiple records is because you include the column Forum_Reply.Account_ID in the select list. Simply because there are multiple account_id replied on the last day.

If you don't care which account_id replied on the last day, you just need one appears in the report, then you can

select Forum_topic.Forum_Topic_Name,
       Forum_topic.Account_id,
       Forum_topic.Forum_Topic_ID,
       max(Forum_Reply.Account_ID), 
       max(forum_reply.Forum_Reply_Date) 
from Forum_Topic 
left outer join forum_reply 
on forum_reply.forum_topic_id=forum_topic.Forum_Topic_ID 
group by  Forum_topic.Forum_Topic_Name,
       Forum_topic.Account_id,
       Forum_topic.Forum_Topic_ID

 

[maswien]

please ignore my previous post, I'll come back

 

[bborissov]

O GOD!

[COLOR=blue]select[/color] Forum_topic.Account_id,
       Tbl2.Account_ID,
       Tbl2.Forum_Reply_Date,
       Tbl2.Forum_Topic_ID,
       Forum_Topic_Name
[COLOR=blue]from[/color] Forum_Topic
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] ([COLOR=blue]SELECT[/color] Forum_Reply.forum_topic_id,
                  Forum_Reply.Account_ID,
                  forum_reply.Forum_Reply_Date
           [COLOR=blue]FROM[/color] forum_reply
           [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] forum_topic_id,
                              [COLOR=#FF00FF]MAX[/color](Forum_Reply_Date) [COLOR=blue]AS[/color] Forum_Reply_Date
                       [COLOR=blue]FROM[/color] forum_reply
                       [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] forum_topic_id) Tbl1
           [COLOR=blue]ON[/color] forum_reply.forum_topic_id   = Tbl1.forum_topic_id AND
              forum_reply.Forum_Reply_Date = Tbl1.Forum_Reply_Date) Tbl2
[COLOR=blue]on[/color] forum_reply.forum_topic_id=Tbl2.Forum_Topic_ID
[COLOR=blue]where[/color] forum_topic.forum_category_id=927

again not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Silx]

Just one error this time bbo,

/* Error: "The column prefix 'forum_reply' does not match with a table name or alias name used in the query." */

 

[bborissov]

So many forums here

[COLOR=blue]select[/color] Forum_topic.Account_id,
       Tbl2.Account_ID,
       Tbl2.Forum_Reply_Date,
       Tbl2.Forum_Topic_ID,
       Forum_Topic_Name
[COLOR=blue]from[/color] Forum_Topic
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] ([COLOR=blue]SELECT[/color] Forum_Reply.forum_topic_id,
                  Forum_Reply.Account_ID,
                  forum_reply.Forum_Reply_Date
           [COLOR=blue]FROM[/color] forum_reply
           [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] forum_topic_id,
                              [COLOR=#FF00FF]MAX[/color](Forum_Reply_Date) [COLOR=blue]AS[/color] Forum_Reply_Date
                       [COLOR=blue]FROM[/color] forum_reply
                       [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] forum_topic_id) Tbl1
           [COLOR=blue]ON[/color] forum_reply.forum_topic_id   = Tbl1.forum_topic_id AND
              forum_reply.Forum_Reply_Date = Tbl1.Forum_Reply_Date) Tbl2
[COLOR=blue]on[/color] Forum_Topic.forum_topic_id=Tbl2.Forum_Topic_ID
[COLOR=blue]where[/color] forum_topic.forum_category_id=927

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Silx]

It works!!

bbo, you the man. And I appreciate all the help from everyone.
Kudos!

I'll probably stick around, because I'm sure I'll need more help down the road.

 

[maswien]

bbo,

your SQL should be able to simplified as:

select Forum_topic.Account_id,
       Tbl1.Account_ID,
       Tbl1.Forum_Reply_Date,
       Tbl1.Forum_Topic_ID,
       Forum_topic.Forum_Topic_Name
from Forum_Topic
left join (SELECT Forum_Reply.forum_topic_id,
                  Forum_Reply.Account_ID,
                  MAX(Forum_Reply_Date) AS Forum_Reply_Date
           FROM forum_reply
           GROUP BY Forum_Reply.forum_topic_id,
                    Forum_Reply.Account_ID ) Tbl1
on Forum_Topic.forum_topic_id=Tbl1.Forum_Topic_ID
where forum_topic.forum_category_id=927

 

[bborissov]

Yep, it could.
That is what happens when you think much complex then you should

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Silx]

I actually tried the simplified version, and got locked into an endless query.

 

[Silx]

ok, I'm back!

Looks like I forgot a very important piece of the query.

Here is the query as it stands, which works awesome!

select Tbl2.Forum_Topic_ID,
       Forum_Topic_Name,
       Forum_Topic_Replies,
       Forum_Topic_LastPost,
       Forum_topic.Account_id as Poster_Account_ID,
       Tbl2.Account_ID as Last_Reply_Account_ID,
       Tbl2.Forum_Reply_Date,
       Forum_Topic_Locked,
       Forum_Topic_Sticky
from Forum_Topic
left join (SELECT Forum_Reply.forum_topic_id,
                  Forum_Reply.Account_ID,
                  forum_reply.Forum_Reply_Date
           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) Tbl2
on Forum_Topic.forum_topic_id=Tbl2.Forum_Topic_ID
where forum_topic.forum_category_id=927

However, In the ACCOUNT table, I need to pull the ACCOUNT.ACCOUNT_USERNAME for each record that we get from this query. And the records which have nulls for some of the topics because of lack of replies need to remain in the list!

I managed to plug in account.account_id = forum_reply.account_id, but then that got rid of all the topics which didn't have replies!

so I guess what I'm asking for is to pull account.username via linked account.account_id and forum_reply.account_id!

halp! lol

 

[bborissov]

How about (not tested at all):

select Tbl2.Forum_Topic_ID,
       Forum_Topic_Name,
       Forum_Topic_Replies,
       Forum_Topic_LastPost,
       Forum_topic.Account_id as Poster_Account_ID,
       Tbl2.Account_ID as Last_Reply_Account_ID,
       Tbl2.Forum_Reply_Date,
       Tbl2.Username,
       Forum_Topic_Locked,
       Forum_Topic_Sticky
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
where forum_topic.forum_category_id=927

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Silx]

Dude, you must write this stuff in your sleep...

Works perfect.

THANKS!

 

[LNBruno]

<off topic>

Isn't it amazing how a "basic query" can so quickly become not?

</off topic>

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]