Page 2 - Query For Forum Threads

Dev Shed Forums Sponsor:

#16

June 28th, 2003, 10:46 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,913

Time spent in forums: 1 Month 4 Weeks 12 h 24 m 34 sec
Reputation Power: 1018

Code:

select OP.Message_title as OriginalPosting 
     , ROP.Message_title as ReplyOriginalPosting 
     , RROP.Message_title as ReplyReplyOriginalPosting 
  from Message OP
left outer
  join Message ROP
    on OP.Message_ID = ROP.Message_Parent
left outer
  join Message RROP
    on ROP.Message_ID = RROP.Message_Parent

check out how the tables are linked through their keys and it should make sense

#17

June 28th, 2003, 11:14 PM

amber141

Contributing User

Join Date: Jun 2003

Posts: 42

Time spent in forums: 59 m 23 sec
Reputation Power: 6

I got a syntax error when I try to run that query. When I remove the 2nd left outer join, it works to retrieve the first reply.

#18

June 29th, 2003, 12:25 AM

amber141

Contributing User

Join Date: Jun 2003

Posts: 42

Time spent in forums: 59 m 23 sec
Reputation Power: 6

Well, I tried doing this.

Code:

SELECT E1.Message_ID, E2.Message_ID, E3.Message_ID
FROM Message E1, Message E2, Message E3
WHERE E2.Message_ID=E1.Message_Parent
AND E3.Message_ID=E2.Message_Parent
ORDER BY E1.Message_ID;

No error but not exactly the result I am looking for. I have another reply for original, which does not show up in the resultset

#19

June 29th, 2003, 06:39 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,913

Time spent in forums: 1 Month 4 Weeks 12 h 24 m 34 sec
Reputation Power: 1018

sorry, maybe i don't understand the difference between message_parent and message_inreplyto

could you show a few rows?

#20

June 29th, 2003, 07:57 PM

amber141

Contributing User

Join Date: Jun 2003

Posts: 42

Time spent in forums: 59 m 23 sec
Reputation Power: 6

Message Table
Message_ID|Message_Parent|Message_InReplyTo|Message_Title
1 0 0 OriginalMessage
2 1 1 ReplyOriginalMessage
3 1 2 ReplyReply to Original Message
4 1 1 ReplyOriginalMessage
.
.
13 1 3 ReplyReplyToOriginalMessage

Message_Parent is the Original Posting.
Message_InReplyTo is the Message_ID of the message that this reply is for.

Also, I have a field in the Message Table Message_Replies that holds the no of replies that this message has. This is currently updated which time there is a reply (increment by 1). This is like a waste of database resource to have a insert then update statement.

How do I retrieve the number of replies by using aggregate function in the same query used to retrieve the rows?

#21

June 29th, 2003, 08:54 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,913

Time spent in forums: 1 Month 4 Weeks 12 h 24 m 34 sec
Reputation Power: 1018

okay, now i get it, having seen examples of the data

the query i gave, which you elaborated on, was joining on message_parent, the wrong column -- it should have been joining onthe inreplyto column

you can do a count at the same time as individual rows only if your database allows derived tables

Last edited by r937 : June 29th, 2003 at 08:59 PM.

#22

June 29th, 2003, 08:58 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,913

Time spent in forums: 1 Month 4 Weeks 12 h 24 m 34 sec
Reputation Power: 1018

okay, had to page back to the front of this thread to double-check

you're using access, so this should work:

Code:

select OP.Message_title as OriginalPosting 
     , ROP.Message_title as ReplyOriginalPosting 
     , RROP.Message_title as ReplyReplyOriginalPosting 
     , ( select count(*)
           from Message
          where Message_Parent = OP.Message_ID ) as Messages
  from Message OP
left outer
  join Message ROP
    on OP.Message_ID = ROP.Message_InReplyTo
left outer
  join Message RROP
    on ROP.Message_ID = RROP.Message_InReplyTo

#23

June 29th, 2003, 09:18 PM

amber141

Contributing User

Join Date: Jun 2003

Posts: 42

Time spent in forums: 59 m 23 sec
Reputation Power: 6

I am still have a syntax error on the query. The message is syntax error(missing operator) in query expression 'OP.Mesage_ID = ROP.Message_InReplyTo left outer joion Message RROP on ROP.Message_ID = RROP.Message_IneplyTo'.

#24

June 29th, 2003, 09:21 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,913

Time spent in forums: 1 Month 4 Weeks 12 h 24 m 34 sec
Reputation Power: 1018

aw, crap, i forgot that access requires pairs of joined tables to be parenthesized:

Code:

select OP.Message_title as OriginalPosting 
     , ROP.Message_title as ReplyOriginalPosting 
     , RROP.Message_title as ReplyReplyOriginalPosting 
     , ( select count(*)
           from Message
          where Message_Parent = OP.Message_ID ) as Messages
  from (
       Message OP
left outer
  join Message ROP
    on OP.Message_ID = ROP.Message_InReplyTo
       )
left outer
  join Message RROP
    on ROP.Message_ID = RROP.Message_InReplyTo

#25

June 29th, 2003, 09:28 PM

amber141

Contributing User

Join Date: Jun 2003

Posts: 42

Time spent in forums: 59 m 23 sec
Reputation Power: 6

Ok I got it right now. Thanks for your help.

Just read this article at
http://searchdatabase.techtarget.co...i537290,00.html

This article has an example on using trees in sql written by Joe Celko. Useful for creating a thread forum with tree structure

Last edited by amber141 : June 29th, 2003 at 10:11 PM.

#26

June 30th, 2003, 05:07 PM

ecit12

Contributing User

Join Date: Jun 2003

Posts: 411

Time spent in forums: 2 Days 12 h 42 m 14 sec
Reputation Power: 7

hmmm..

I was just thinking..

if this is ur DB

# Message
1 Msg1
2 Msg2
2 Msg2Child
2 Msg2ChildChild
3 Msg3

instead of all that complex stuff

why not do this..

obtain the count of the distinct numbers that occur.. and also retrive the message where date is the least? would that work

select count(AutoNumber), TOP MSGDate
From urTable
Group By AutoNumber
Order By MsgDate Ascending;

something like that..

#27

June 30th, 2003, 10:25 PM

amber141

Contributing User

Join Date: Jun 2003

Posts: 42

Time spent in forums: 59 m 23 sec
Reputation Power: 6

Code:

SELECT COUNT(Message_InReplyTo),MIN(Message_Date)
FROM Message_
WHERE Message_Parent = 1
GROUP BY Message_InReplyTo

yuh, it does select the correct no of replies and the date of the first reply but will not be able to add other fields to the query, like message_id for identification possible.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: