Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 67C972E2DD2
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 25 Jan 2008 19:05:52 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 20388-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 25 Jan 2008 19:05:50 -0400 (AST)
Received: from hs-out-2122.google.com (hs-out-0708.google.com
 [64.233.178.250])
	by postgresql.org (Postfix) with ESMTP id 82D9C2E2DC8
	for <pgsql-sql@postgresql.org>; Fri, 25 Jan 2008 19:05:49 -0400 (AST)
Received: by hs-out-2122.google.com with SMTP id k27so877022hsc.3
	for <pgsql-sql@postgresql.org>; Fri, 25 Jan 2008 15:05:48 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	bh=/nAXgFYyW/HThWaCv6WWSM0ittFo8Vu/JX4WFCJMTno=;
	b=NZFniYttiE8E6NiTBcM+8QPmIbcAMA+sHAun3cfMg78un63YARuNjVNfdvx7aoNX9xrNGoygwAeevtHIHiwpagttwi42wn9cKb26pyHvcqplnBzrN+IKjFuprRVrYv8Y9I5bMpBwCMR9ACpDYK51a6+xHLmFQcmSmPrUlIu94jQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=RvZNlEmKzaFltJ0eWWrb5eP4tbm6JvDNNvTxwUP/u5WLcaJzKmpbs20ecqBDU3EpfsZBD0gdNtRgDOE4lXc+FW0DKAHL1m3nR/6cMnFsI3PJ9Nm9v6CbwcArgYaVPS3gIRHfz73LJHrNnnhfe3u4sJYnjV98c0Ifwu7aCZRgrxI=
Received: by 10.142.215.5 with SMTP id n5mr1679377wfg.11.1201302347568;
	Fri, 25 Jan 2008 15:05:47 -0800 (PST)
Received: by 10.142.174.7 with HTTP; Fri, 25 Jan 2008 15:05:47 -0800 (PST)
Message-ID: <b35603930801251505t42f014aap8ae2b3d89bfe521f@mail.gmail.com>
Date: Sat, 26 Jan 2008 12:05:47 +1300
From: "Andrej Ricnik-Bay" <andrej.groups@gmail.com>
To: "Phillip Smith" <phillip.smith@weatherbeeta.com.au>
Subject: Re: Extract interdependent info from one table
Cc: "sql pgsql" <pgsql-sql@postgresql.org>
In-Reply-To: <010e01c85f08$5d52e570$9b0014ac@wbaus090>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <b35603930801241932j30499965i3d75229beb24e13e@mail.gmail.com>
	<010e01c85f08$5d52e570$9b0014ac@wbaus090>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200801/205
X-Sequence-Number: 30058

On 25/01/2008, Phillip Smith <phillip.smith@weatherbeeta.com.au> wrote:

> Absolutely clear as mud :P
sorry ... I'm not too good with expressing my SQL needs, I'm afraid,

Thanks for trying :}


In the end I got it sorted with the following
select
  dm.docnum,
  dm.alias1,
  dm.docname,
  dm.alias2 as "Status",
  dm2.alias2 as "Reply"
from docmaster as dm
left outer join
  ( select
      alias1,
      subclass_alias,
      alias2,
      entrywhen
    from docmaster
    where subclass_alias in ('Reply','MinTR')
    and alias2 in ('PENDING','COMPLETED')
  ) as  dm2
on dm2.alias1 = dm.alias1
where dm.subclass_alias='Post'
  and alias2 in ('PENDING','REDRAFT');


Cheers,
Andrej

-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm