(SQL Query, Email) Need a query to find "thread items" based on a field in the same table.

Question

Hi All,

I'm looking to perform a somewhat complex SQL Query and I'm having a bit of a time wrapping my head around what I need to do. I can't seem to figure out a JOIN that will work for my situation.

Basically, my issue is that I have a table of EMAIL objects with various headers separated out and I'm trying to implement THREADING. In my estimation, I need to do the following:

1) Select all items that have no "IN-REF-TO" value -- as those are likely thread starters

2) Select all items (from the same table) that have any of the previously found "REF-NUM"s inside their "IN-REF-TO" fields

3) order by date ... I think I can handle this part =P

I'd like to have all data returned in a single recordset, but I'm completely open to suggestions. Can you loop with SQL? If anyone feels that this isn't the way to go about it then, by all means, please suggest some other mechanism. For the record, I didn't design the database and there is very little chance that the structure will be changed in the next 6 mths or so.

Answer 1

Something like this may work for you:

select *
from Email 
order by coalesce(IN-REF-TO, REF-NUM) desc, CREATE-DATE

It will list the newest topic first (assuming REF-NUMs are created in ascending order), followed by its replies in ascending order, and then the next topic, etc.

It assumes there is a NULL value in IN-REF-TO when it is the topic starter. if that is not the case, the order by will need to be modified. I can do so when you post more details.

Edit:

Ok, try something like this:

select e1.*
from Email e1
left outer join Email e2 on e1.IN-REF-TO like '%' + e2.REF-NUM + '%'
order by coalesce(e2.REF-NUM, e1.REF-NUM), CREATE-DATE