Self Join 1

[tofuTnT]

Hi All,

I have a table like the following:
MsgID, ParentMsgID, Title
1, 0, Title 1
2, 1, Title 2
3, 2, Title 3
4, 1, Title 4
5, 0, New Title
6, 5, New Title 2

Basically, message 1, 2, 3, 4 belong to the same thread. 5, 6 belong to another thread.
What I'd love to have are two tables based on privous table.
MsgID, ThreadID, Title
1, 1, Title 1
2, 1, Title 2
3, 1, Title 3
4, 1, Title 4
5, 2, New Title
6, 2, New Title

ThreadID, FirstMsgID, LastMsgID
1, 1, 4
2, 5, 6

Can anyone show me a way to get those two tables?

Thanks,
tofuTnT

 

[lynchg]

How do you identify the thread in the first table?

 

[tofuTnT]

If the parentMsgID is 0, then it is a new thread.

if the table is like
MsgID, ParentMsgID, Title
1, 0, Title 1
2, 1, Title 2
3, 2, Title 3
4, 1, Title 4
5, 0, New Title
6, 5, New Title 2

then the structure of messages should be
Title 1 (Thread 1)
Title 2
Title 3
Title 4
New Title (Thread 2)
New Title 2

Hope that is clear enough.

tofuTnT

 

[lynchg]

Are the records always in the order you show in your example?

If so, you are going to need to code this, but you can do it.

First, add a field to your table, call it ThreadID.

Create a recordset and order it by the MsgID field, and declare a counter variable, set it equal to 0. Step thru your recordset and update the ThreadId value to equal your counter variable. Each time you encounter a '0' in the ParentMsgID field increment your counter, make sure you do this before updating the ThreadID values.

This will give you all of the values you need in one table, then you can query against that to get the data you talk about seeing in your other tables. You can create those tables if you want but you don't need to.

The data for your second "table" should be obvious, for the third "table" run this query:

Select ThreadID, Min(MsgID), Max(MsgID)
From yourTable
Group By ThreadID;

HTH

 

[PHV]

Something like this ?
SELECT T.ThreadID, T.FirstMsgID, Max(C.MsgID) AS LastMsgID
FROM (
SELECT Count(*) AS ThreadID, A.MsgID AS FirstMsgID
FROM tblThreads AS A INNER JOIN tblThreads AS B ON A.MsgID>=B.MsgID
WHERE A.ParentMsgID=0 AND B.ParentMsgID=0 GROUP BY A.MsgID
) AS T INNER JOIN tblThreads AS C ON T.FirstMsgID=C.ParentMsgID
GROUP BY T.ThreadID, T.FirstMsgID
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tofuTnT]

To answer HTH's question:
Unfortunatelly, the records is not always in the order that my sample shown.
There could be another row that says
MsgID, ParentMsgID, Title
1, 0, Thread 1 - Msg 1
2, 1, Thread 1 - Msg 2
3, 2, Thread 1 - Msg 3
4, 1, Thread 1 - Msg 4
5, 0, Thread 2 - Msg 1
6, 5, Thread 2 - Msg 2
7, 2, Thread 1 - Msg 5
I will try to write a module to get the tables. At the beginning I was hoping I could write queries to get the tables I wanted.

PHV, I will try you solution and let you know.

 

[tofuTnT]

PHV,
That was amazing. The query gave me the second table as I have dreamed of. I still have to study your query to see the logic.

At the mean time, is it possible to write a query to get the first table that I wanted to have? I am sure that should be easy for you.

Thanks you all.

tofuTnT

 

[tofuTnT]

PHV,
I wonder if you are still following this thread.
I have been trying to write a query that is able to give me the first table. Basically, the table add a new field to the originally one to identify which thread the message belongs to.
Thanks again.

tofuTnT

 

[PHV]

For a max of 2 levels.
Create a query named qryThreadsLevel0:
SELECT A.MsgID, Count(*) AS ThreadID, A.Title
FROM tblThreads AS A INNER JOIN tblThreads AS B ON A.MsgID>=B.MsgID
WHERE A.ParentMsgID=0 AND B.ParentMsgID=0
GROUP BY A.MsgID, A.Title;
Create a query named qryThreadsLevel1:
SELECT A.MsgID, B.ThreadID, A.Title
FROM tblThreads AS A INNER JOIN qryThreadsLevel0 AS B ON A.ParentMsgID=B.MsgID;
Create a query named qryThreadsLevel2:
SELECT A.MsgID, B.ThreadID, A.Title
FROM tblThreads AS A INNER JOIN qryThreadsLevel1 AS B ON A.ParentMsgID=B.MsgID;
And now to get the infos you want:
SELECT * FROM qryThreadsLevel0
UNION SELECT * FROM qryThreadsLevel1
UNION SELECT * FROM qryThreadsLevel2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tofuTnT]

That query worked very well for 2 levels of messages.
but the table that I have has a lot more levels than that.

Right now, I just wrote a function in VBA as below:

Function getParent(messageID As Integer)
    Dim dataRow As Object
    Set dataRow = Application.CurrentDb.OpenRecordset("SELECT parentMsgID FROM tblThreads WHERE msgID = " & messageID)
    
    If dataRow.RecordCount <> 1 Then
        Exit Function
    End If
    
    Dim parentMsgID As Integer
    parentMsgID = dataRow.Fields(0)
    
    If (parentMsgID = 0) Then
        getParent = messageID
        Exit Function
    End If
    
    getParent = getParent(parentMsgID)
    
End Function

and add a new column ThreadID to the table
and run and update query

UPDATE tblThreads SET tblThreads.ThreadID = getParent([tblThreads]![MsgID]);

I then ran another query

SELECT ThreadID, MIN(MsgID) as FirstMsg, Max(MsgID) as LastMsg
FROM tblThreads
GROUP BY ThreadID

There is it..

Thanks all !!

tofuTnT