Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id DDE792E00D1 for ; Thu, 24 Jan 2008 23:36:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 98915-01-2 for ; Thu, 24 Jan 2008 23:35:57 -0400 (AST) Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.229]) by postgresql.org (Postfix) with ESMTP id 698DE2E0146 for ; Thu, 24 Jan 2008 23:32:53 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id l8so369836nzf.28 for ; Thu, 24 Jan 2008 19:32:52 -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:mime-version:content-type:content-transfer-encoding:content-disposition; bh=W9y9xnFxhv2PHiz5Reum/743E7eqD9NJtZik710RDWc=; b=BAHZ4OWG6UP12QDzw7163MfVBAcf7WpJD5wdQF5Oo+rkDrMe+zli2FpOCfUSrJCnhgev6R9gbDc46Ar5QGhXLw1b/t+U70/jvry38XWZW5hU6QSbq6F4Pe5g5jGFaLvEBXuY5AnntQTIWQkfXV389Fd13mDQRdjzJgg1QbPSRdg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=Ppb5YHGKqzdWXdPzijMx1HL7nWIK45eo415hznsK0UlnPa+CtCQjZNnY0yKMPCNRgB50CPXK6JDTiWUiehn1nDfXhjCYlhvLMpFNjHmznYSLfzG3IE7N64puN4cc8JXkQ6w/sZ+5lif4tdpr5Y/e67tybCYAqiGsQs1D1xjcdjg= Received: by 10.142.132.2 with SMTP id f2mr930458wfd.205.1201231971509; Thu, 24 Jan 2008 19:32:51 -0800 (PST) Received: by 10.142.174.7 with HTTP; Thu, 24 Jan 2008 19:32:51 -0800 (PST) Message-ID: Date: Fri, 25 Jan 2008 16:32:51 +1300 From: "Andrej Ricnik-Bay" To: "sql pgsql" Subject: Extract interdependent info from one table MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/186 X-Sequence-Number: 30039 Hi Guys, I can't quite wrap my head around this one ... I have \d docmaster Table "public.docmaster" Column | Type | Modifiers ----------------+------------------------+----------- alias1 | integer | alias2 | character varying(25) | subclass_alias | character varying(25) | docnum | integer | not null version | integer | docname | character varying(255) | Indexes: "docmaster_docnum_key" UNIQUE, btree (docnum) with the following data: # select * from docmaster ; alias1 | alias2 | subclass_alias | docnum | version | docname --------+-----------+----------------+--------+---------+----------------- 3589 | Completed | Inquiry | 653218 | 1 | My greater doc2 3587 | Pending | Post | 653216 | 3 | My great doc1 3588 | Draft | Reply | 653217 | 1 | My great doc2 3587 | Draft | Reply | 653219 | 2 | My greater doc4 (4 rows) Now I want to find inquiries (subclass_alias = 'Inquiry'), list their status and also (if there's another row that a) has the same alias1, a subclass of Reply and a status (alias2) of pending or redraft.... how do I achieve this? What I have is select docnum, alias1, alias2, subclass_alias from docmaster where (alias1 = ( select alias1 from docmaster where subclass_alias = 'Post' and ( alias2 = 'Pending' or alias2 = 'Redraft' ))) and ( alias2 = 'Pending' or alias2 = 'Draft' ) and subclass_alias <> 'Post' ; docnum | alias1 | alias2 | subclass_alias --------+--------+---------+---------------- 653219 | 3587 | Redraft | Reply (1 row) What I'd really like is to BOTH Post AND reply, with the alias2 for both. Hope this was as clear as mud? :) 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