Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 89935475F98 for ; Fri, 1 Mar 2002 12:42:31 -0500 (EST) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 814006; Fri, 01 Mar 2002 09:42:35 -0800 From: "Josh Berkus" Subject: Re: sub SELECT To: Charles Hauser , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Fri, 01 Mar 2002 09:42:35 -0800 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/11 X-Sequence-Number: 6893 Hey, Chuck! How's the project going, so far? > A search using 4 of these 6 columns [clone.(project plate p_row > p_column) = (894 001 A 01)] will return 2 results: > 894 001 A 01 x 1 > 894 001 A 01 y 1 > where 'x|y' = clone.read, and '1' = clone.ver. > > For each of these two (894001A01x1, 894001A01y1), I want to find the > corresponding contig. Why not a JOIN rather than a sub-select? It would be faster to execute. > So, a two part query: > 1st find ALL clones defined by clone.(project plate p_row p_column) > 2nd find ALL contigs related to each clone. I think you're doing this the hard way. What's wrong with the following? SELECT contig.assembly_date,contig.contig_no,contig.ver FROM clone JOIN clone_contig USING (clone_id) WHERE clone.project = '1024' AND clone.plate = '001' AND clone.p_row = 'A' AND clone.p_column = '01'; > WHERE clone.clone_id = ( > SELECT clone.clone_id > FROM clone > WHERE clone.project = '1024' AND > clone.plate = '001' AND > clone.p_row = 'A' AND > clone.p_column = '01' If you want a subselect, then you want: WHERE clone.clone_id IN ( SELECT clone.clone_id FROM clone WHERE clone.project = '1024' AND clone.plate = '001' AND clone.p_row = 'A' AND clone.p_column = '01'); But I do not think that a subselect is actually what you need. -Josh