X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E9400476B39 for ; Fri, 16 May 2003 12:53:26 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 83943-05 for ; Fri, 16 May 2003 12:53:25 -0400 (EDT) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id 57F2F476B31 for ; Fri, 16 May 2003 12:53:25 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id 36C5643A611; Fri, 16 May 2003 12:53:25 -0400 (EDT) From: "J Sensel" <"jsensel ateggheadsdotcom"@news.hub.org> X-Newsgroups: comp.databases.postgresql.sql References: <6729af8d.0305160646.d9ab2d2@posting.google.com> Subject: Re: Chain/Thread Problem Date: Fri, 16 May 2003 12:53:08 -0400 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: Lines: 46 X-Complaints-To: abuse@giganews.com X-DMCA-Notifications: http://www.giganews.com/info/dmca.html X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.1 To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/350 X-Sequence-Number: 13515 Sammy.. A quick and dirty approach is something like this: id|threadid|emailid|referenceid|pathlevel --------------------------------------- 1|t101|e323|null|0 2|t101|e545|e323|1 3|t101|e822|e545|2 4|t101|e999|e323|1 etc.. Do the sorting when you insert the records. Then they can be quickly & easily retrieved. Otherwise you're looking at a recursive query. Not very efficient. This way is crude but allows the engine to optimize queries with indexes. HTH Jim "Sammy" wrote in message news:6729af8d.0305160646.d9ab2d2@posting.google.com... > Hi, > > I have a SQL problem for you all. What SQL qurey would I have to make > on the following table to extract the chain (thread) of emails in a > conversation. > > > id|emailid|referenceid > ----------------------- > 1| e125 | Null (Start of chain) > 2| r1fe | e125 (2nd letter in chain) > ... > ... > n| e4ju | r1fe (3rd in chain) > > ie > a sucessulf qurey would return rows 1,2 & n because they are all in > the chain. > > Hope you can help me with my problem,